Jump to Cell Based on Value in Another Cell (Macro LibreOffice VBA)

I want to be able to jump to a cell based on the user entering a date (in B14). In column F, I have a list of dates (starting at row 8). So far, I have =MATCH(B14,F8:F373)+7 (in B15) which calculates which row the right date is in, and returns a number.

I need to write a macro in LibreOffice VBA that will select the cell in that row, in column G. So far, I have:

sub jump

dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

Doc = ThisComponent
Sheets=Doc.Sheets
sheet=Sheets.getByName("ThisYear")

dim args1(0) as new com.sun.star.beans.PropertyValue

thisrow =sheet.getCellByPosition(15,2).getValue()

args1(0).Name = "ToPoint"
args1(0).Value = (G,thisrow)

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

end sub

But it doesn't accept the value being in the form (column, row). I have seen before I need something like args1(0).Value = "G15" but how can I include the variable? (I have tried using 7 instead of G but this doesn't help.)

I have mentioned the sheet name, sheet=Sheets.getByName("ThisYear"), but it is all within the one sheet so ideally I wouldn't want to specify this so I could use the macro in different sheets.

I am new to VBA so please reply with the whole sub.

Thanks!

Solution

I understand that you are having trouble figuring out how to change the cell selection in LibreOffice Calc. Here is a code snippet that shows you how. In short, you identify a targeted range (oRange) (which below is a single cell at location 3,3), and then pass that object to the .select() method.

Sub ChangeSelection
  oSheets = ThisComponent.Sheets
  oSheet = oSheets.getByIndex(0)
  oRange = oSheet.getCellByPosition(3,3)
  ThisComponent.CurrentController.select(oRange)

End Sub

LibreOffice uses uses the UNO API documented here. If you want to browse the properties and methods for an object, use MsgBox oObject.DBG_properties or .DBG_methods, you usually will be able to find the right thing eventually. The code snippet in your question returns an error that I could not immediately figure out.