Running a macro and getting an error

English
,
#1

I’m trying to run the macro bellow and I get the following message error when I try to run it:
BASIC runtime error.
An exception occurred
Type: com.sun.star.uno.RuntimeException
Message: .

and it stops on line:
oRangeA = oSheet.getCellRangeByName(“A2:A2047” & oSheet.Rows.Count)

Anybody can help fixing the problem??
Thanks

REM  ***** This macro will compare the values in Column A
REM  with those in Columns D and E, and format the corresponding
REM  cells in Column D or E based on the length of the value in Column C.
REM  Green indicates a length greater than 1, and red indicates a length less than 2.

Sub CompareAndFormatCells()
    Dim oSheet As Object
    Dim oRangeA As Object
    Dim oRangeD As Object
    Dim oRangeE As Object
    Dim oRangeC As Object
    Dim i As Long
    
    ' Set the active sheet (adjust sheet name if needed)
    oSheet = ThisComponent.Sheets.getByName("BD-direta")
    
    ' Define the ranges for columns A, D, E, and C
    oRangeA = oSheet.getCellRangeByName("A2:A2047" & oSheet.Rows.Count)
    oRangeD = oSheet.getCellRangeByName("D2:D2047" & oSheet.Rows.Count)
    oRangeE = oSheet.getCellRangeByName("E2:E2047" & oSheet.Rows.Count)
    oRangeC = oSheet.getCellRangeByName("C2:C2047" & oSheet.Rows.Count)
    
    ' Loop through each row in column A
    For i = 0 To oRangeA.Rows.Count - 1
        Dim aValue As String
        Dim dValue As String
        Dim eValue As String
        Dim cValueLength As Integer
        
        aValue = oRangeA.getCellByPosition(0, i).String
        dValue = oRangeD.getCellByPosition(0, i).String
        eValue = oRangeE.getCellByPosition(0, i).String
        cValueLength = Len(oRangeC.getCellByPosition(0, i).String)
        
        ' Compare values in column A with D and E
        If aValue = dValue Or aValue = eValue Then
            ' Check length in column C
            If cValueLength > 1 Then
                ' Turn cell in column D or E green
                oRangeD.getCellByPosition(0, i).CellBackColor = RGB(0, 255, 0) ' Green
                oRangeE.getCellByPosition(0, i).CellBackColor = RGB(0, 255, 0) ' Green
            Else
                ' Turn cell in column D or E red
                oRangeD.getCellByPosition(0, i).CellBackColor = RGB(255, 0, 0) ' Red
                oRangeE.getCellByPosition(0, i).CellBackColor = RGB(255, 0, 0) ' Red
            End If
        End If
    Next i
End Sub
#2

Please, always mark line with get error

    ' Define the ranges for columns A, D, E, and C
    oRangeA = oSheet.getCellRangeByName("A2:A" & oSheet.Rows.Count)
    oRangeD = oSheet.getCellRangeByName("D2:D" & oSheet.Rows.Count)
    oRangeE = oSheet.getCellRangeByName("E2:E" & oSheet.Rows.Count)
    oRangeC = oSheet.getCellRangeByName("C2:C" & oSheet.Rows.Count)

but, in generally, is better work in current range with cursor, instead entire column

#3

Hello and welcome!
In fact, this will be written much shorter and without a macro at all (but we’ll talk about that later - you’re interested in your macro now, not conditional formatting)
I read several times the phrase from the macro commentary “Green indicates a length greater than 1, and red indicates a length less than 2.” And I still didn’t understand the meaning.
I tried to figure it out from the code but failed. Maybe you can show me a sample spreadsheet with example data?

#4

the line where I get the error is:
oRangeA = oSheet.getCellRangeByName(“A2:A2047” & oSheet.Rows.Count)

as far as I can understand you are telling me not to define the end of the columns (in this case all of them end in the row 2047)?

#5

I already tried the conditional formating and I gave up because of the structure of the spreadsheet.
Turning the cells of the columns D and E green or red depends of the Len() value of column C… the Len() can be either <2 or >1.
Don’t know how to send you an extract of the sheet.

#6

See the upload button in the middle of the toolbar?
Bildschirmfoto von 2024-02-26 18-40-34

Conditional formatting supports formula expressions.

#7

Thanks I saw it now. sending you a small extract… the columns A, C, D and E have 2047 raws. the column C has formulas using vlookup for other sheet.
sample.ods (18.6 KB)

about conditional formating …yes I know but because of the structure of the sheet I gave up.

#8

102640.ods (25.1 KB)

#9

What a pity that in this short fragment there was not a single line for which in D or E there would be a value from A :slight_smile:

May be so?

Sub setColors() 
Dim oSheet As Variant
Dim oCursor As Variant
Dim oDataArray As Variant
Dim i As Long 
	oSheet = ThisComponent.getSheets().getByName("BD-direta")
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oDataArray = oCursor.getDataArray()
	For i = 1 To UBound(oDataArray)
		With oSheet.getCellRangeByPosition(3, i, 4, i)
			If (Trim(oDataArray(i)(0)) = Trim(oDataArray(i)(3))) Or (Trim(oDataArray(i)(0)) = Trim(oDataArray(i)(4))) Then
				If Len(oDataArray(i)(2)) < 2 Then
					.CellStyle = "Bad"
				Else 
					.CellStyle = "Good"
				EndIf 
			Else 
				.CellStyle = "Default"
			EndIf 
		End With 
	Next i
End Sub
#10

Don’t understand…
for example in column A row 4 you have 2P1 which is the value you find in column D row 2…
or for example
the column A row 9 where you find 3P2 … you find this value in column E row 4
now I sent you the sample with columns D and E with wath should be the result of the macro I’m looking for…
sample.ods (22.4 KB)
the principle of the macro is the following (sorry for my english not being very clear)…
This macro will compare the values in Column A (from row 2 till last one filled which in my case is the 2047 row) with those in Columns D and E… and format the corresponding
cells in Column D or E based on the length of the value in Column C row of the value of column A.
Green indicates a length greater than 1, and red indicates a length less than 2.

#11

Thanks for the help… I’m sending the expected result in the columns F and G…
Every time a value
102640_expected.ods (24.7 KB)
from the colum A is found in column E or D it should read the correspondent Len() of the row in the column C and if <2 or >1 the cell in column D or E becomes red or green.

#12

Oh… Your code compares values from the same row i

Try this

Sub setColors() 
Dim oSheet As Variant
Dim oCursor As Variant
Dim oDataArray As Variant
Dim i As Long, j As Long 
	oSheet = ThisComponent.getSheets().getByName("BD-direta")
	oCursor = oSheet.createCursor()
	oCursor.gotoEndOfUsedArea(True)
	oDataArray = oCursor.getDataArray()
	For i = 1 To UBound(oDataArray)
		With oSheet.getCellRangeByPosition(3, i, 4, i)
			CellStyle = "Default"
			For j = 1 To UBound(oDataArray)
				If (Trim(oDataArray(j)(0)) = Trim(oDataArray(i)(3))) Or (Trim(oDataArray(j)(0)) = Trim(oDataArray(i)(4))) Then
					If Len(oDataArray(i)(2)) < 2 Then
						.CellStyle = "Bad"
					Else 
						.CellStyle = "Good"
					EndIf 
					Exit For 
				EndIf 
			Next j
		End With 
	Next i
End Sub
#13

I’m a rooky trying to develop macros. :blush:… so probably my code is not correct.
I hope I made my self clear with the small description and with the expected result I sent you.

#14

Yes, it’s much clearer now. I hope the code from my previous comment does exactly what you wanted.

#15

Almost there… the file I’m sending you shows the result of your macro.

Estrutura_teste_with your macro.xlsx (222.9 KB)

#16

Sorry, I do not understand the rule why some cell should be green or red.

#17

Estrutura_teste_expected result.xlsx (223.4 KB)
this should be the result.
PS- the macro you sent is very slow when running!! sorry!!

#18

the rule takes into account what is in the column C.
In my worksheet the cells in column C have a formula that look for a value in another sheet… and the result be: “blank” (which is not true because there is a formula) or they return a name which has more than 1 character.

#19

Bildschirmfoto von 2024-02-27 03-10-32
Why is D2 red, E2 green, D3 and E3 red, D4 and E4 green? I don’t get it.

#20

Ok. Let me try to explain.
The macro should read each of the rows of column A. Every time it reads a new cell in A gets the value of Len() of the same row in column C… then looks in all values of column D and E if the value of column A match(Note - the cell values of A2 and A3 are the only ones that don’t exist in columns D and E)
If the value is found it should turn the color of the cell in the column D or E to green or red taking into account if Len() is >1 or <2.
using the sample I sent:
-starting with A4 (value2P1)… it reads the Len(C4) and gets the result of 1… then looks in Column D from top to bottom if the A4 match… in this case it matches in D2 cell (value 2P1)… as it found the value of A4 and as Len(C4) is <2 the D2 cell should become red
-then it reads A5 (value2P2)… gets Len(C5) and gets the result of 5… compares A5 with values in column D… and doesn’t find A5… compares A5 with values in column E and find the value in E2 (value 2P2)… so the E2 cell shall become green because Len(c5) is >2
-then it reads the A6 (value2M1)… gets Len(A6) =1 … looks in column D and gets a match in D3… so as Len(a6) <2 turns the D3 cell red.
-then it reads the A7 (value2M2)… gets Len(A7) =1 … looks in column D and doesn’t get a match… so looks in column E and get a match with E3… so as Len(A7) <2 turns the E3 cell red.
-then it reads the A8 (value3P1)… gets Len(A8) =3 … looks in column D and gets a match in D4… so as Len(A8) >1 turns the D4 cell green
-then it reads the A9 (value 3P2)… gets Len(A9) =3 … looks in column D and doesn’t get a match… so looks in column E and get a match with E4… so as Len(A9) >1 turns the E4 cell green.
-then it reads the A10 (value3P3)… gets Len(A10) =5 … looks in column D and gets a match in D5… so as Len(A10) >1 turns the D5 cell green.
… and so on…
The column A,C D and E values end up in the row 2047.
expected result

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.