formulaopenoffice-basic

How to use variable in openoffice basic's formular?

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

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:

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?

oCell.Formula = "=LOOKUP(arg;$'rule'.$A$2:$A$9;$'rule'.$B$2:$B$9)"

Solution

  • Use quoting syntax similar to the line where you defined arg.

    id = 2
MsgBox("=LOOKUP(A" & id & ";$'rule'.$A$2:$A$9;$'rule'.$B$2:$B$9)")

    Result:

    =LOOKUP(A2;$'rule'.$A$2:$A$9;$'rule'.$B$2:$B$9)