1

I'm writing a piece of code to split large cells onto new rows, while copying the rest of the cells on that row to the new row, and part of this exercise involves finding any pipe characters and starting a new row with the rest of the cells contents from the pipe. I have almost everything working, but for some reason certain pipes aren't counted and don't start a new line.

The below code is the snippet that does the comparison. For each cell I am checking every character to see if it is a pipe or not

For i = 1 To length
        If Mid(subString, i, 1) = "|" Then
        
            'Insert new row
            .Cells(rowNo, 1).Offset(1, 0).EntireRow.Insert
            
            'Copy the other cells into this new row
            .Cells(rowNo, 1).Offset(1, 0).Value = .Cells(rowNo, 1).Value
            .Cells(rowNo, 2).Offset(1, 0).Value = .Cells(rowNo, 2).Value
            'increment sequence
            .Cells(rowNo, 4).Offset(1, 0).Value = .Cells(rowNo, 4).Value + 1
            
            'Display text for new row from character | onwards
            .Cells(rowNo, 3).Offset(1, 0).Value = Mid(subString, i, Len(subString))
            
            'Display text from character position 1 to |
            .Cells(rowNo, 3).Value = Mid(subString, 1, i)
            
            subString = .Cells(rowNo, 3).Offset(1, 0).Value
            
            'Increment Row No
            rowNo = rowNo + 1
        End If
        
    Next i

An example of where this is required is below. My code is starting a new row before Reference, but not before NFPA and I can't work out why

gineer).  ||Reference Standards: |NFPA

Any help is appreciated.

Thanks

DSTL
  • 45
  • 1
  • 10
  • 2
    Take a look at this: https://stackoverflow.com/questions/41003866/excel-macro-split-comma-separated-entries-to-new-rows and instead of comma look for pipes. – Scott Craner Mar 30 '21 at 21:20
  • Thanks Scott, appreciate the input. I was looking at my problem from the wrong angle. Once I find a pipe im moving on to the next cell, so once I correct that it should work as required! – DSTL Mar 30 '21 at 21:50

0 Answers0