I want to concatenate the lines of a cell, but somehow my loop is not working and gives a weird result. (picture below)
Could somebody help me out what am I doing wrong? I have tried so hard to find my mistake but seems I can't.
Thank you very much in advance.
Could somebody help me out what am I doing wrong? I have tried so hard to find my mistake but seems I can't.
Thank you very much in advance.
VBA Code:
Public Function EveryLineToNewLine(snippet As String) As String
Dim i As Integer
Dim output As String
Dim elozo As Integer
Dim debugg As String
i = 1
output = ""
previousPosition = 1
'I have 5 lines, i want to run the loop 4 times (so 4 "new line" will be inserted,
'resulting in 5 lines
Do While i < 5
'we look for new line --> starting at 1, in the cell passed as argument, and looking for new line character
chrPoz = InStr(previousPosition, snippet, Chr(10), vbBinaryCompare)
'then we concatenate it to the end of our string and we insert a new line
output = output & Mid(snippet, previousPosition, chrPoz) & Chr(10)
'then we want to start the search next time after the current new line +1
previousPosition = chrPoz + 1
'increase loop count
i = i + 1
'and notice character indexes to see the number of loops and found new lines indexes
debugg = debugg & chrPoz & ","
Loop
'adding last line (will return all character after last new line character)
output = output & Mid(snippet, previousPosition)
EveryLineToNewLine = output & "" & "debug:" & debugg
End Function
Last edited: