Help Converting VBA code to LibreOffice Calc code

[Mike Guest98]

I have working VBA code that I have rewritten to be used in LibreOffice Calc but it’s not working. The VBA code is searching through three ranges of cells for any sets of numbers (eg 1-15 or 3-99) where the second number is 19 or higher and then it does a count which is written to cell E18. I have the VBA code below and the non working Cal code below that.



Do you know why it’s not working. If you have any questions please ask. Thank you for looking.


Working VBA code


Sub Locate19PLUSnumbers()


Dim rng As Range, c As Range

Set rng = Range("E23:E175,J23:J175,O23:O175")

With CreateObject("scripting.dictionary")

For Each c In rng

If Val(Mid(c, InStr(1, c, "-") + 1)) > 19 Then .Item(c.Address(0, 0)) = Empty

Next

If .Count = 0 Then [E18] = 0 Else [E18].Resize(.Count) = Application.Transpose(.Keys)

End With

MsgBox "Finished"

End Sub





(Non-Working) LibreOffice Calc Code – Version 6.1.5.2


Sub Locate19PLUSnumbers()

Dim rng As Dim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1), c As Dim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)

Set rng = Dim oSheet as Object[n]oSheet = ThisComponent.CurrentController.ActiveSheet[n]oSheet.getCellRangeByName($1)("E23:E175,J23:J175,O23:O175")

With CreateObject("scripting.dictionary")

For Each c In rng

If Val(Mid(c, InStr(1, c, "-") + 1)) > 19 Then .Item(c.Address(0, 0)) = Empty

Next

If .Count = 0 Then [E18] = 0 Else [E18].Resize(.Count) = Application.Transpose(.Keys)

End With

MsgBox "Finished"

 

[Mike Guest98]

Please delete post.

 

[Joe4]

Please delete post.

We typically only delete posts if private/sensitive data is posted.

If you no longer need an answer, just replying back saying something to that effect is sufficient (if you have a solution, we usually advise people to post the solution in case others with similar questions come upon this thread).