writing a macro to look in sheet A to a specific column with values and generate in a already existing sheet B in a specific location of sheet B a table with two columns. How can I create a new table header for that table? already tried with setstring and setvalue but returns error stating “property or method not found”
Do you mean
ThisComponent.getSheets().getByName("B").getCellRangeByName("D3:E3").setDataArray(Array(Array("Header 1","Header 2")))
I’m using this macro and the line where it gives the error it’s in bold
Sub GenerateUniqueValueTable()
Dim oSheetA As Object
Dim oSheetB As Object
Dim oSourceRange As Object
Dim oTargetRange As Object
Dim oUniqueValues As Object
Dim oCell As Object
Dim i As Long
' Set the source sheet (Sheet A) and source range
oSheetA = ThisComponent.getSheets().getByName("BD-direta")
oSourceRange = oSheetA.getCellRangeByName("P1:P2047")
' Set the target sheet (Sheet B) and target range
oSheetB = ThisComponent.Sheets.getByName("Estatistica")
oTargetRange = oSheetB.getCellRangeByName("B64:C64")
' Create a new table header
**oTargetRange.setValue("Unique Value")**
oTargetRange.getCellByPosition(1, 0).setString("Count")
' Get unique values and their counts
oUniqueValues = CreateUnoService("com.sun.star.container.UniqueElementsContainer")
For Each oCell In oSourceRange
If Not oUniqueValues.has(oCell.String) Then
oUniqueValues.insert(oCell.String, 1)
Else
oUniqueValues.replaceByIndex(oUniqueValues.indexOf(oCell.String), oUniqueValues.getByIndex(oUniqueValues.indexOf(oCell.String)) + 1)
End If
Next
' Populate the target sheet
For i = 0 To oUniqueValues.getCount() - 1
oSheetB.getCellByPosition(2, i + 1).setString(oUniqueValues.getByIndex(i))
oSheetB.getCellByPosition(3, i + 1).setValue(oUniqueValues.getByIndex(i))
Next
End Sub
line with error between **
setValue() for numbers, for single cell only, not for range
oTargetRange.setDataArray(Array(Array("Unique Value","Count")))
Thanks a lot. It’s working. 