[Solved] How to use variable in Basic formula?

[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?

[cwolan]

Try this:

Code: Select all

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

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