PDA

View Full Version : Solved: parsing text & combinations



vzachin
01-31-2008, 09:07 AM
hi,

i have data in columns a, b & c.
column a only has 1 item
the items in columns b & c are separated by a space.
i need the item in col a to go to col g. then
i want to extract the items separated by the spaces from col b and place into col h,
and
do the same from col c and place into col i.
then i want all the possible combinations for col g,h & i

please take a look at attachment


is this possible?

thanks
zach

qff
02-03-2008, 04:20 AM
Hi

I think this will do what you ask.

Sub myPairs()
Dim startrow As Integer
Dim mycol As Integer
Dim mystr1 As String
Dim mystr2 As String
Dim mypart As String
Dim part1 As Variant
Dim part2 As Variant
Dim myrow As Integer

startrow = 5
mycol = 7
myrow = startrow

Do Until Cells(startrow, 1) = ""
mypart = Cells(startrow, 1)
mystr1 = Cells(startrow, 2)
mystr2 = Cells(startrow, 3)

part1 = Split(mystr1, Chr(32))
part2 = Split(mystr2, Chr(32))

For i = 0 To UBound(part1)
For j = 0 To UBound(part2)
Cells(myrow, mycol) = mypart
Cells(myrow, mycol + 1) = part1(i)
Cells(myrow, mycol + 2) = part2(j)
myrow = myrow + 1
Next
Next

startrow = startrow + 1

Loop
End Sub

qff
02-03-2008, 04:20 AM
oops! seemed to have posted twice.

vzachin
02-06-2008, 04:55 AM
hi gff,

that's amazing! if you get a moment, can you please explain (in layman's terms) how ubound works?


thanks again for the coding
zach