Solved: Parse a string to Range

[Adamski]

I have a string in the format:

T1 vbTab T2 vbTab T3 vbNewLine
U1 vbTab U2 vbTab U3 vbNewLine
V1 vtTab V2 vbTab V3

It is not in an external file.


Is there an easy way to set this delimited string to a range, specifing the top left cell.
The equivalent of doing a paste in that cell.


Note: I don't have individual variables, just the full string. I could change the string format but I would like it to be in a format that can be copy pasted to a range.

Thanks

[Kenneth Hobs]

Paste to A1 or A1:C3?

[Adamski]

The string is not on the clipboard

[Kenneth Hobs]

I would rather not do work that does not meet your need. Please state where the string value should be put as I requested.

[Adamski]

Sorry, I interpreted your reply as "is the solution to paste the string into eiter A1 or A1:C3?".

I want to just specify an individual cell (eg. A1) and populate as many rows/columns as needed by the string.

[Adamski]

Clarification: It is cell A2 that is the top left cell at the moment. This may change though.

[frank_m]

Getting to the top left cell is easy enough using offset.

For more clarification should the result in this example use 3 columns and 3 rows?
A2 = T1, B2 = T2, C2 = T3
A3 = U1, B3 = U2, C3 = U3
A4 = V1, B4 = V2, C4 = V3

[Kenneth Hobs]

You can easily modify this to accept an input string and output start cell for the range r.
[VBA]Sub ParseStringToRange()
Dim T1 As String, T2 As String, T3 As String
Dim U1 As String, U2 As String, U3 As String
Dim V1 As String, V2 As String, V3 As String
Dim s As String, r As Range, a() As String, aRow() As String
Dim nRows As Long, nCols As Integer
Dim iRow As Long, iCol As Integer

T1 = "T1"
T2 = "T2"
T3 = "T3"
U1 = "U1"
U2 = "U2"
U3 = "U3"
V1 = "V1"
V2 = "V2"
V3 = "V3"

s = T1 & vbTab & T2 & vbTab & T3 & vbNewLine
s = s & U1 & vbTab & U2 & vbTab & U3 & vbNewLine
s = s & V1 & vbTab & V2 & vbTab & V3

Set r = Range("A1")

a() = Split(s, vbNewLine)
nRows = UBound(a)
nCols = UBound(Split(a(1), vbTab))
For iRow = 0 To nRows
aRow() = Split(a(iRow), vbTab)
For iCol = 0 To nCols
r.Offset(iRow, iCol).Value2 = aRow(iCol)
Next iCol
Next iRow
End Sub[/VBA]

[Adamski]

Sorry it's been a while.
Thanks, for that.
I tried setting each row at once with an array which was much faster but made all the cells 'text'.
I'll stick with setting each cell individually