String to Range?
 

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

String to Range?
 

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



"Kevin G" wrote in message
news:KjSSb.3026$7O2.909@edtnps89...
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