Basically, I'm trying to turn this... To this... This is the code I've come up with...
Code: Select all
Sub Main
Doc = ThisComponent
Sheet = Doc.Sheets(0)
c = Sheet.createCursor
c.gotoEndOfUsedArea(false)
'Variable declarations
Dim iCol, iRow As Integer 'Counter variables for loops
Dim ColArray() As String 'Store all columns in a single row
Dim FirstColArr() As String 'Hold each comma separated value in 1st column
Dim DataArray(0,0) As String 'Store all values collected from sheet
'Stores number of rows & columns filled with data
LastRow = c.RangeAddress.EndRow
LastCol = c.RangeAddress.EndColumn
'Loop that repeats for all the rows that contain data
For row = 0 To LastRow
'Store all columns of a single row
For col = 0 To LastCol
ColArray(col) = Sheet.getCellByPosition(col, row).String
Next col
'Store each value in 1st column thats separated by a comma
FirstColArr = split(ColArray(0), ",")
'Remove any spaces prior to each value stored
For i = Lbound(FirstColArr) To Ubound(FirstColArr)
If Left(FirstColArr(i), 1) = " " Then
FirstColArr(i) = Mid(FirstColArr(i), 2, 15)
End If
Next i
'Store all columns of a single row of data for each comma
'separated value of 1st column
For n = 0 To Ubound(FirstColArr)
Redim Preserve DataArray(iCol, iRow)
DataArray(0, iRow) = FirstColArr(n)
For iCol = 1 To LastCol
DataArray(iCol, iRow) = ColArray(iCol)
Next iCol
iRow = iRow + 1
Next n
Next row
iRow = iRow - 1
For y = 0 To iRow
For iCol = 0 To LastCol
Sheet.getCellByPosition(iCol, y).String = DataArray(iCol, y)
Next iCol
Next y
End Sub
Dim ColArray() As String
to
Dim ColArray(3) As String in the variable declarations
AND
Redim Preserve DataArray(iCol, iRow)
to
Redim Preserve DataArray(3, iRow)
then it'll work fine. But, then its also locked to only looking at 4 columns instead of a variable number of columns & rows. I can't figure this out for the life of me. Any idea what I'm doing wrong here?