[Solved] How to use variable in Basic formula?

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
luofeiyu
Posts: 21
Joined: Thu Sep 14, 2017 2:11 am

[Solved] How to use variable in Basic formula?

Post by luofeiyu »

rule is a worksheet's name,the command works fine:

Code: Select all

oCell.Formula = "=LOOKUP(A2;$'rule'.$A$2:$A$9;$'rule'.$B$2:$B$9)" 
I want to make the first A2 in LOOKUP with a variable:

Code: Select all

   for id=2 to NumRows
        oCell = Sheet.getCellrangeByName("B"&id)
        arg = "A"&id
        oCell.Formula = "=LOOKUP(arg;$'rule'.$A$2:$A$9;$'rule'.$B$2:$B$9)" 
    next id
 
The arg will not assign its value into LOOKUP ,how to fix it?
Last edited by MrProgrammer on Fri Oct 28, 2022 7:17 pm, edited 3 times in total.
Reason: Tagged ✓ [Solved] -- MrProgrammer, forum moderator
LibreOffice 4.3.3.2 on Debian 8
cwolan
Posts: 129
Joined: Sun Feb 07, 2021 3:44 pm

Re: How to use variable in basic's formular?

Post by cwolan »

Try this:

Code: Select all

oCell.Formula = "=LOOKUP("&arg&";$'rule'.$A$2:$A$9;$'rule'.$B$2:$B$9)" 
OpenOffice 1.1.5 – 4.1.15
LibreOffice 6.4.7 – 7.6.5
Windows 7,10,11 64-bit
luofeiyu
Posts: 21
Joined: Thu Sep 14, 2017 2:11 am

Re: [SOLVED] How to use variable in basic's formular?

Post by luofeiyu »

Code: Select all

oCell.Formula = "=LOOKUP(" & arg & ";$'rule'.$A$2:$A$9;$'rule'.$B$2:$B$9)"
Or directly write it as:

Code: Select all

oCell.Formula = "=LOOKUP(" & "A" & id & ";$'rule'.$A$2:$A$9;$'rule'.$B$2:$B$9)"
LibreOffice 4.3.3.2 on Debian 8
Post Reply