[Solved] Increment cell row reference

[erlwebmail@gmail.com]

I'd like to write a macro to use a WHILE/WEND loop to copy values from two rows of two cells into four cells in a different single row location in the sheet, for example: $AM$14, $AN$14, $AM$15, $AN$15 values copied into $A$16, $E$16, $F$16, $H$16, then moving down two rows on the source locations and one row on the destination row. (Sorry, but that's the way the source data are organized.) This doesn't lend itself to the RANGE method, as far as I can tell.
I've tried incrementing row addresses with

Code: Select all

ActiveCell.Row=ActiveCell.Row + 1

but it says that ActiveCell.Row is read-only.
Can you please suggest a way to use a counter to formulate and store a string representation of an address (like "$A$16"), then use

Code: Select all

ActiveSheet.Range(destCellStringVbl).Select

? I'm rebuilding an Excel VBA macro, and documentation for OpenOffice Calc macros is hard to find.

[RoryOF]

I'm rebuilding an Excel VBA macro, and documentation for OpenOffice Calc macros is hard to find.

The most comprehensive work on all OpenOffice macros are the books (free to download) by Andrew Pitonyak. These are at
www.pitonyak.org

[JeJe]

You can copy a value from one cell to another like this. Or you can use .string if its the string you want.

Code: Select all

with thiscomponent.currentcontroller.activesheet
.getCellRangeByName("A1").value = .getCellRangeByName("C10").value
end with

You can select a cell (or other range) and use xtransferable to transfer the contents regardless of what they are like this

Code: Select all

with thiscomponent.currentcontroller
.select .activesheet.getCellRangeByName("A1")
trans = .gettransferable
.select .activesheet.getCellRangeByName("B1")
.inserttransferable trans
end with

Edit:
or you can use .getCellRangeByPosition which makes incrementing the cell easier.

Asc("A") and Chr(65) are functions which can help you convert the column letter to a number and back which you can use to increment the column if you want to stick with cell names. Asc("B") is Chr(66) and so on.

[erlwebmail@gmail.com]

Thanks. Your "Select a Cell" code worked. ...sorta!
Next issue: the source cell contains a reference to another cell in another sheet, so what gets pasted into the destination cell is displayed as "#REF!" and contains "=$'Recipe Log'.#REF!16$'Recipe Log'.E21" . This is a rather "ugly" reference within that 2nd sheet, probably generated by importing the spreadsheet into CALC.
Can you think of a macro function that would evaluate the value of the destination cell? Interestingly, strings come through fine, but numeric values don't.

[Zizi64]

Next issue: the source cell contains a reference to another cell in another sheet, so what gets pasted into the destination cell is displayed as "#REF!" and contains "=$'Recipe Log'.#REF!16$'Recipe Log'.E21" .

Because you have copied a STRING.
In this case you must copy the FORMULA.

You can get the type of the cell content of the source cell by usage some API commands, and the you need use the .String, the .Value or the .Formula conditionally ( IF(...) )



https://forum.openoffice.org/en/forum/v ... hp?t=49217

Edit: Sorry, I see now, that the #REF! message it inside the formula.

Please upload your sample file here.

[Villeroy]

What are you trying to program here if you don't even know the most fundamental basics about referencing?

Get a blank spreadsheet and enter into D4 =C3 which is a relative reference to the neighbour cell at the top-left corner.
Copy or drag D4 upwards and to the right.
In row 1 you get =C#REF! because there is no neighbour on top of row 1.
I column A you get =#REF!3 because there is no neighbour left of column A.

I'm rebuilding an Excel VBA macro, and documentation for OpenOffice Calc macros is hard to find.

Quite obviously, you never wrote any VBA. You can't set the row number of a cell. We are not here to do your work. We are not human macro recorders.

[erlwebmail@gmail.com]

No need to be snarky! I've doing VBA in Excel since the late '90's, BASIC since the Trash-80 days, FORTRAN since 1964... Just not the VBA variant used in LibreOffice Calc. It's VERY different from Excel VBA.

What I need to write into the cell in question is some means of doing the equivalent of "paste special->paste special->values only->numbers", to overwrite a cell's contents with an actual number. There's no problem overwriting a cell's contents with text using JeJe's method, but it won't overwrite with an actual number.

What it's doing now is pasting a reference to another cell in the same sheet, which contains a reference to a cell in another sheet, which contains a reference to another cell in that sheet, which is stored as a number. It blows up on "Err=509" as stored. Clear as mud, huh? That other sheet contains numerical and text data for numerous sausage recipes, and the macro I'm working on copies the desired sausage recipe to the sheet that does calculations on that particular recipe. The Calc VBA loader imported the following entry into $A$16:

Code: Select all

=$'recipe log'.#ref!16'Recipe Log'.E21`$'Recipe Log'.G18

What I need is the actual number being referenced.

Can anyone provide a helpful suggestion? ...something like a VALUE(...) function in the macro, perhaps? Something like...

Code: Select all

...
trans = .gettransferable
.select .activesheet.getCellRangeByName("B1")
.inserttransferable VALUE(trans)

[Zizi64]

Just not the VBA variant used in LibreOffice Calc. It's VERY different from Excel VBA.

Because it is not VBA.
The Apache OpenOffice and the LibreOffice has a very simple Basic variant named in the early times: StarBasic.

You can call the API functions of the office suite from your Basic code. And the API functions are really different from any VBA.
API: Application Programming Interface.

[JeJe]

If you just want to set the value of a cell its this

Code: Select all

 thiscomponent.currentcontroller.activesheet.getCellRangeByName("A1").value = 6

Edit:

Or put

=A1

in the formula bar for a cell so its the same value that's in A1

[Lupp]

To the OQer: If you actually think to need to write user code for AOO/LibO, you should start with the famous texts by AndrewPitonyak.
There's mainly OpenOffice Macros Explained and Useful Macro Information from https://www.pitonyak.org/oo.php .
As already told: What you need to undersatnd aren't special constructs of the Basic (which actually is a simple version), but the way the API is used.

However, most tasks in Calc people seek macros for are better solved by well designed formulas or using special tools available from the UI. Nonetheless it's sometimes more fun to write a macro - and in rare cases that's even efficient or "necessary". With Writer and other components it's similar.

[erlwebmail@gmail.com]

Thanks, folks, for trying.
JeJe's suggestion,

Code: Select all

thiscomponent.currentcontroller.activesheet.getCellRangeByName("A1").value = 6

would work fine if I were inserting a number. Unfortunately, I'm trying to get a number by referencing another cell, and it puts the cell reference in there and "blows up":

Code: Select all

with thiscomponent.currentcontroller
.select .activesheet.getCellRangeByName(srcCellPos)
trans = .gettransferable
.select .activesheet.getCellRangeByName(destCellPos).value
.inserttransferable trans
end with

It's a reference to a cell on another sheet that references another cell on that sheet, which references another cell in that sheet, and inserts

Code: Select all

=$'recipe log'.#ref!16'Recipe Log'.E21`$'Recipe Log'.G18

into the destination cell, when I intended it to store a numerical value, which starts off with, for example, 0.0036855 I'm wondering if it thinks the value is the reference string instead of a number.

Sorry that it's so complicated, but it seemed like an easier way to go than doing a database lookup. In retrospect, maybe not. Maybe I'll have to rewrite the intermediate cells to ".value" references (which I'll try next), or use a simpler data storage arrangement with table lookup functions. Aarrgghh!

...any other thoughts? BTW thanks for the Pitonyak reference. It's very helpful.

[erlwebmail@gmail.com]

Well, after a fair amount of trial & error + blind luck, I figured it out. So now, how do you mark this SOLVED?
My previous code stored a reference to another cell, rather than the number stored in that cell. If you previously store an actual number in the destination cell, writing a reference to that cell stores the actual number. Here's the code (omitting superfluous lines):

Code: Select all

' Loop to store ingredient quantities as numbers, not references
        destCounter=16 ' init destination at $A$16
        srcCounter=11  ' init source at $AM$11
      While (destCounter < 28) ' each line of ingredients
      ' 1st (amt) value
        srcCellPos="$AM$"& Cstr(srcCounter)
        destCellPos="$A$"& Cstr(destCounter)
        ActiveSheet.Range(srcCellPos).Select ' get source val
	srcCellVal=ActiveCell.value
        ActiveSheet.Range(destCellPos).Select 'put destination val
'... use the following line if destination cells 
'... have not been initialised to numbers
'activesheet.Range(destCellPos) = 5 
	ActiveSheet.Range(destCellPos) = srcCellVal
' .
' ...other cell retrievals go here......
' .
'...bump active source & destination row counters down the columns
        srcCounter = srcCounter + 2 ' unpack every other row in source column
        destCounter = destCounter + 1 ' every row in destination column
      Wend

Thanks again to JeJe, whose suggestion I first didn't appreciate, but which actually solved the problem.

[JeJe]

You edit the subject line, putting [Solved] in front.