String to Range?

[Kevin G]

Hi again, I have already posted help on this but I can't get it to work. I
would like try again.
Thanks to Tom Ogilvy I can convert a Range of cells (J9:J520) to a single
cell string. (See code below)

Sub RangeToArray()
'Tom Ogilvy creator
Dim sStr As String
Dim cell As Range
sStr = ""
For Each cell In Range("J9:J520")
sStr = sStr & cell.Value & ", "
Next
sStr = Left(sStr, Len(sStr) - 2)
ActiveCell.Value = sStr
End Sub

I now need to convert this single cell string back to the cell range
(J9:J520). The converted single cell string will have 512 values and may
consist of 2, 3 or 4 digit values. Example: 598, 593, 1002, 598, 565, 98,
etc...

Is this possible? How can it be done?

Thanks, Kevin Graham

 

[Chip Pearson]

Kevin,

Try something like the following. Change the value of the
variable S to the actual string containing the data values.

Dim S As String
Dim Arr As Variant
S = "1,2,3"
Arr = Split(S, ",")
Range("J9:J520").Value = Application.Transpose(Arr)


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com