PDA

View Full Version : Solved: Parse a string to Range



Adamski
08-03-2011, 06:49 AM
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
08-03-2011, 07:34 AM
Paste to A1 or A1:C3?

Adamski
08-03-2011, 09:08 AM
The string is not on the clipboard

Kenneth Hobs
08-03-2011, 09:22 AM
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
08-04-2011, 02:33 AM
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
08-04-2011, 05:53 AM
Clarification: It is cell A2 that is the top left cell at the moment. This may change though.

frank_m
08-04-2011, 07:14 AM
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
08-04-2011, 08:56 AM
You can easily modify this to accept an input string and output start cell for the range r.
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

Adamski
08-17-2011, 04:19 AM
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