gmaxey
12-15-2022, 06:47 AM
Hello, I can do some basic stuff with VBA in Excel but I'm stuck on a process and hoping someone here might help.
I can create a one dimensional array of data a write it to a new row in and existing workbook. Here is the basic process:
varValues = Split(Smith,A1,B1,C1,A2,B2,C2,A3,B3,C3?, ?,?)
m_oSheet.UsedRange 'Refresh UsedRange
lngLastRow = m_oSheet.UsedRange.Rows(m_oSheet.UsedRange.Rows.Count).Row
For lngIndex = 0 To UBound(varValues)
m_oSheet.Cells(lngLastRow + 1, lngIndex + 1).value = varValues(lngIndex)
Next lngIndex
The row single row of data looks like this:
Smith A1 B1 C1 A2 B2 C2 A3 B3 C3
This same process can be run again with a new set of data and the result could look like this:
Smith A1 B1 C1 A2 B2 C2 A3 B3 C3
Roberts A1 B1 C1 A2 B2 C2 A3 B3 C3
I need the data to look like this:
Smith A1 B1 C1
Smith A2 B2 C2
Smith A3 B3 C3
Roberts A1 B1 C1
Roberts A2 B2 C2
Roberts A3 B3 C3
My data set will always look the same. The source for each data set is a Word form where a named person inputs information sets about planned travel e.g., Trip A, Trip B and Trip C
I don't know if there is a function in Excel that I could run after the single rows are created or it would need to create and input a two dimensional array. If so, what code would I use to write the four data rows for each input.
Cross posted here as well: https://www.excelforum.com/word-programming-vba-macros/1395714-break-a-data-set-into-multiple-rows.html
Thank you.
I can create a one dimensional array of data a write it to a new row in and existing workbook. Here is the basic process:
varValues = Split(Smith,A1,B1,C1,A2,B2,C2,A3,B3,C3?, ?,?)
m_oSheet.UsedRange 'Refresh UsedRange
lngLastRow = m_oSheet.UsedRange.Rows(m_oSheet.UsedRange.Rows.Count).Row
For lngIndex = 0 To UBound(varValues)
m_oSheet.Cells(lngLastRow + 1, lngIndex + 1).value = varValues(lngIndex)
Next lngIndex
The row single row of data looks like this:
Smith A1 B1 C1 A2 B2 C2 A3 B3 C3
This same process can be run again with a new set of data and the result could look like this:
Smith A1 B1 C1 A2 B2 C2 A3 B3 C3
Roberts A1 B1 C1 A2 B2 C2 A3 B3 C3
I need the data to look like this:
Smith A1 B1 C1
Smith A2 B2 C2
Smith A3 B3 C3
Roberts A1 B1 C1
Roberts A2 B2 C2
Roberts A3 B3 C3
My data set will always look the same. The source for each data set is a Word form where a named person inputs information sets about planned travel e.g., Trip A, Trip B and Trip C
I don't know if there is a function in Excel that I could run after the single rows are created or it would need to create and input a two dimensional array. If so, what code would I use to write the four data rows for each input.
Cross posted here as well: https://www.excelforum.com/word-programming-vba-macros/1395714-break-a-data-set-into-multiple-rows.html
Thank you.