0

I set up this code to identify a character in a column and split the contents of that column into as many rows as that character is present. It's been working for a while but now I'm receiving a run-time error. I tried to debug the code and it appears that the bolded line is the cause of the problem. Does anyone have an idea of how to fix this type of problem?

Sub splitByColB()
    Dim r As Range, i As Long, ar
    Set r = Worksheets("SheetNAme").Range("H999999").End(xlUp)
    Do While r.Row > 1
        ar = Split(r.Value, ";")
        If UBound(ar) >= 0 Then r.Value = ar(0)
        For i = UBound(ar) To 1 Step -1
            r.EntireRow.Copy         '<------------ Line causing problem
            r.Offset(1).EntireRow.Insert
            r.Offset(1).Value = ar(i)
        Next
        Set r = r.Offset(-1)
    Loop
End Sub
YowE3K
  • 23,852
  • 7
  • 26
  • 40
dner
  • 57
  • 5
  • I'm not sure about the type mismatch but why are you offsetting up when your r range is End(xlUp)? – jivko Jun 14 '17 at 01:31
  • Won't splitting it into columns function of excel work? follow this link https://support.office.com/en-us/article/Split-text-into-different-columns-with-the-Convert-Text-to-Columns-Wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7 – Romcel Geluz Jun 14 '17 at 01:41
  • The formula is meant to take data that looks like this: A | B | 1; 2; 3 and turn it into a set of three rows of A | B | 1, A | B | 2, A | B | 3 but for a set of 5 "AB" columns and 8000 total rows – dner Jun 14 '17 at 01:42
  • Code is working for me. It's certainly a strange line of code to give a "Type mismatch" error. What is the value of `r.Row` when it crashes? Is there anything strange about that row? – YowE3K Jun 14 '17 at 02:19
  • Why do you need this line at all? You are not using the copy anywhere. – RADO Jun 14 '17 at 02:21
  • 1
    @RADO the `insert` line will use it to create a copy by insertion. – A.S.H Jun 14 '17 at 02:22
  • Off-topic: [splitByColB](https://stackoverflow.com/a/42425847/4926357) should be renamed `splitByColH` here. :P – A.S.H Jun 14 '17 at 02:23
  • 1
    difficult to assess. We can only speculate. Is your worksheet protected? do you have merged cells? any information could be useful. – A.S.H Jun 14 '17 at 02:26
  • Executes perfectly here. Also when r.Row is hidden, r is empty, when it's a merged cell, etc. (Excel 2016). Type mismatch is a bit strange to me, since the types are correct everywhere, it seems. Anyway I can't seem to break it. – Rik Sportel Jun 14 '17 at 07:20

0 Answers0