object error/ formulaR1C1

[minmark]

I have record macro and rewrite them as following with loop:
DailyUsage:
Dim i As Integer, j As Integer


Range("J15").Select
For i = 4 To 34
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(MATCH(RC,Drug!C1,0)),"""",INDEX(Drug!C2,MATCH(RC,Drug!C1,0)))"




Cells(15, 7 + i).Select
Next i


But the message remind me error '1004' object define error ....(My operation system is Chinese traditional text, don't know exact English notice.)
How can I set the range with integer or other variables well in this case?

Thanks.

 

[StephenCrump]

Your variable, i, means something to the VBA code, but not to Excel. Therefore you need to pass only the value of i to Excel, like this:

ActiveCell.FormulaR1C1 = _
    "=IF(ISERROR(MATCH(RC[" & i & "],Drug!C1,0)),"""",INDEX(Drug!C2,MATCH(RC[" & i & "],Drug!C1,0)))"

You will be able to replace your loop with a single line of code:

Range("J15:AM15").Formula = ....

but I'm not sure what your intended formula is. Is it working as you envisage?

Also, if you have excel 2007 or later, you can replace the IF(ISERROR(YourFormula),"",YourFormula) with the simpler IFERROR(YourFormula,"")