How to use variable in openoffice basic's formular?

Question

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)"

Answer 1

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)