Loop through string and write to range

[stapuff]

I am trying to figure out how to determine the end of each line in my string.

My data is returned like

item = 2
item = 13
item = 9
item = 1742596

I want item1 returned to sheet1 range A1, item2 returned to sheet1 range A2, item3 returned to sheet1 range A3

Thanks,

Kurt

 

[poolhall]

suppose your data is in B1, try this:

Sub test3()
Dim myString As String, myRange As Range, r As Byte

myString = Range("B1").Value
Set myRange = Range("A1")

r = Len(myString) - Len(Replace(myString, Chr(10), ""))

Do Until n = r
    myRange.Offset(n).Value = Left(myString, InStr(1, myString, Chr(10), vbTextCompare) - 1)
    myString = Replace(myString, myRange.Offset(n).Value & Chr(10), vbNullString)
    n = n + 1
Loop
myRange.Offset(n).Value = myString

End Sub

 

[stapuff]

Hey pool...appreciate the post back.

r = 25

N gets to 7 then errors out Invalid procedure call or arguement

your thoughts,

Kurt

 

[poolhall]

is it possible that some of the items are the same, like item = 2, item = 2, etc?

 

[stapuff]

It looks like where it errors out there are 2 chr(10) in a row and the code only gets rid of the first one.

Kurt

 

[poolhall]

let's then just replace two consecutive chr(10) with one like this:

Sub test3()
Dim myString As String, myRange As Range, r As Byte

Range("B1").Value = Replace(Range("B1").Value, Chr(10) & Chr(10), Chr(10))
myString = Range("B1").Value
Set myRange = Range("A1")

r = Len(myString) - Len(Replace(myString, Chr(10), ""))

Do Until n = r
    myRange.Offset(n).Value = Left(myString, InStr(1, myString, Chr(10), vbTextCompare) - 1)
'    myString = Replace(myString, myRange.Offset(n).Value & Chr(10), vbNullString)
    myString = Right(myString, Len(myString) - Len(myRange.Offset(n).Value) - 1)
    n = n + 1
Loop
myRange.Offset(n).Value = myString

End Sub

I also changed one line to take care of the situation where there are matching items.

 

[stapuff]

pool - still getting the same results

 

[stapuff]

sorry...missed you line edit.

works!

the first and last 3 cells return the empty square box still.

your thoughts,

kurt

 

[poolhall]

deleted

 

[Rick Rothstein]

pool - still getting the same results

Where is your original data located at?

Is the "item = " part included in each one?