Need help fixing this simple code

Madmaxneo · February 5, 2025, 3:27am

Windows 11 Pro (Build 26100)
Libre Office Version: 24.8.4.2 (X86_64)
I may not need to say this because many of you have replied to my posts before but, I am not a coder and a few on here have helped me a great deal in the past. I have learned a little here and there but I really don’t create macros that often to get used to using it.
So, despite not wanting to ask/bother you all again to help me set up a macro code (to help replace the macros that were deleted during an update for Libre Office) I decided to use ChatGPT, and here I am again.
I am getting a BASIC runtime error from the code it gave me. I told ChatGPT about it and it changed the code but even the correction is getting the same error.
This is the full error: BASIC runtime error. Property or method not found: getRange
This is the code:

Sub AddStatNewToStatCurr
    Dim oDoc As Object
    Dim oSheet As Object
    Dim oStatNew As Object
    Dim oStatCurr As Object
    Dim i As Long
    Dim numRows As Long
    Dim statNewCell As Object
    Dim statCurrCell As Object

    ' Get the current document and sheet
    oDoc = ThisComponent
    oSheet = oDoc.Sheets(0) ' Change to the relevant sheet if needed

    ' Access the named ranges StatNew and StatCurr
    oStatNew = oDoc.NamedRanges.getByName("StatNew")
    oStatCurr = oDoc.NamedRanges.getByName("StatCurr")
    
    ' Get the range of StatNew
    Dim statNewRange As Object
    statNewRange = oStatNew.getRange()

    ' Get the number of rows in StatNew (assuming both ranges have the same number of rows)
    numRows = statNewRange.Rows.Count

    ' Loop through each row and add the values
    For i = 0 To numRows - 1
        ' Get the corresponding cell in StatNew and StatCurr
        statNewCell = statNewRange.getCellByPosition(0, i)  ' StatNew column, row i
        statCurrCell = oStatCurr.getRange().getCellByPosition(0, i) ' StatCurr column, row i
        
        ' Add the value from StatNew to StatCurr and replace StatCurr value
        statCurrCell.Value = statCurrCell.Value + statNewCell.Value
    Next i
End Sub

I want this macro (linked to a button) to take the numbers in the named column of StatNew and add them to the numbers in the named column of StatCurr, then replace the current numbers in StatCurr with the new total.

Villeroy · February 5, 2025, 7:29pm

And why is it necessary to write a macro for this trivial task?

Lupp · February 5, 2025, 7:54pm

Possibly the task isn’t described to enough detail to understand for what reason you think to need a macro and a button to run it.
If so: shall the button do it for all (many) rows or only for …(?)
Assuming your column StatCurr can contain ENTERED data you surely should not change the contents.
If you want to get he effect for exactly the row where you just entered a new value (content), tell so, and explain how you would get back to a valid state if an error occurred.

Always attach an example file showing “What I have” and “What I want”.

If you quote something you got as an answer by “artificial intelligence” you must precisely also quote the question. Otherwise nobody can figure out where a “misunderstanding” might have occurred.
Better let it be. Up to now this site should be dedicated to questions, comments, and answers based on biological intelligence.

fpy · February 5, 2025, 8:25pm

Where did all my macros go? - #13 by Wanderer

Madmaxneo · February 5, 2025, 11:08pm

It helps a great deal with calculations on the sheet. The macros in this sheet automate what originally was something that needed to be done manually for each and every cell, along with several other things that need to be done to finalize the current use of the sheet. The macros in the buttons speed things up quite a bit.
The buttons are only to be hit when the editing is done and the sheet needs to be updated/calculated.