View Full Version : [SOLVED:] chr 10
remy988
10-10-2013, 06:21 PM
hi,
i have a string that is separated by chr(10):
Range("c2") = "BIG BEAR" & Chr(10) & "BIG APPLE" & Chr(10) & "BLUE BUG"
how do i extract the strings and list them in range("d2:d4")
thanks
rem
remy988
10-11-2013, 10:32 AM
i don't know how to edit my first post.
i found this code
Dim splitVals As Variant
Dim totalVals As Long
splitVals = Split(Range("c11").Value, Chr(10))
totalVals = UBound(splitVals)
Range(Cells(1, 5 + 1), Cells(1, 5 + 1 + totalVals)).Value = splitVals
that places the data on row 1 beginning in column 5 through the number of columns that the split finds.
how can i list the data down instead of across?
thanks
rem
remy988
10-11-2013, 10:45 AM
sorry for the repost. i have it figured out.
rw = 1
For i = LBound(splitVals) To UBound(splitVals)
Range("d" & rw) = splitVals(i)
rw = rw + 1
Next i
mancubus
10-12-2013, 01:18 AM
hi.
you dont need a loop here.
Sub split_to_range()
Dim splitVals As Variant
splitVals = Split(Range("C2"), Chr(10))
Range("D2").Resize(UBound(splitVals) + 1) = Application.Transpose(splitVals) 'same column, ie, write to rows. Range("D2:D4") populated.
'Range("D2").Resize(, UBound(splitVals) + 1) = splitVals ''same row, ie, write to columns. Range("D2:F2") populated.
End Sub
nor a variable:
Sub split_to_range()
Range("D2").Resize(UBound(split(cells(2,3),vblf)) + 1) = Application.Transpose(split(cells(2,3),vblf))
End Sub
remy988
10-19-2013, 04:35 PM
thanks mancubus & snb
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.