Need help fixing this simple code

English
#1

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.

Where did all my macros go?
#2

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

#3

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.

#4

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

#5

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.

#6

You have a spreadsheet at hand. A spreadsheet is a simplified programming language for non-programmers. Just add the two columns with a third column and paste-special numbers to replace old values with the calculated ones.

#7

It’s hard to explain without going into detail on the entire sheet but these macros make things so much easier. I didn’t design the sheet just for me, it’s also so my friends can use it. Automation like this using buttons is extremely handy when there are other factors involved when finalizing what is being done at the time. When you click the buttons there are literally hundreds of simple calculations going on that take a mere second or two with the click of the button, especially the one that combines all the macros into one button push. This spreadsheet has multiple pages with loads of calculations going on along with a tables section that a lot of the formulas reference when working on the sheet. I am trying my best to make things as simple as possible so the time spent on the sheet is minimal comparative to when you do even some of it manually. Also with as much going on at the finalization when working on the sheet it would be easy to forget the simple things this macro does which would be bad the next time the sheet is needed to be edited.

#8

But you can not program. A spreadsheet can do amazing things without programming other than the simple formula language.

#9

I’m sorry but you’re not making much sense.
You asked why write a macro for this trivial task and I explained why, then you mention that I can’t program and how a spreadsheet can do amazing things without programming other than the simple formula language. Which I know and am pretty good with spreadsheets minus using macros.
What would you suggest doing what I asked without using macros? This is no other way that I have seen or tried that will work for what I want this to do.
I have a set column of cells called StatCurr, in the rows these refer to there are editable named cells called StatInc. StatInc is editable with numbers. There are other cells in those same rows that pertain to both these named sets of cells, then there are other cells throughout the workbook that these cells interact with.
Whatever you put in StatInc affects cells in other areas, and if you like the effect then you can choose to add it to StatCurr to set it permanently, but when you do StatInc needs to be cleared out so it’s not still counted the next time you need to do this. But, there are other things you can do in this workbook before you choose to make everything “permanent” and if something doesn’t add up you can go back and change it before you hit the buttons with the macros to finalize everything.

#10

Prepare:
StatSum: =StatNew+StatCurr

Use:
Copy StatSum
Select StatCurr
Paste-Special values.

As a recorded macro:
ask117571.ods (34.0 KB)

#11

There’s more code in that then in the copy and clear macro you’re debating this against. Not to mention that it does not even clear out the StatNew column, which is also part of the function I need.

#12

How to record this macro:
Call Tools>Macros>Record macro
Enable macro recording in the advanced options if the command is unavailable.
Notice the additional toolbar with the stop button.

  1. In the name box (left of formula bar) or in the navigator window(F5) select named range StatSum
  2. Copy (Ctrl+C)
  3. Select named range StatNew
  4. Clear contents (Del key)
  5. Select named range StatCurr
  6. Clear contents (Del key)
  7. Paste-special (Ctrl+Shift+V) and click button [Values]
  8. Click the stop button.
  9. In the following dialog, choose a module (or create a new one) and enter a macro name in the top left box.
    This macro should be stored in the document or in the document’s template, because it refers to named ranges that do not exist in other documents.

And as @Lupp already indicated, that the whole work flow (replacing “old” data with “new” data) is bad practice.

1 Like

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