Cell reference in VBA code with Excel Functions

[Insomniac53]

I don't use Excel that often, and VBA even less often, so I suspect I'm missing some simple concept. I have this line of code in a macro:

ActiveCell.Offset(0, 7).Formula = "=VLOOKUP($C21,Lists!$C$1:$E$99,3,FALSE)"

It works, except that it doesn't update the $C21 to $C22, $C23 etc. when called to create new rows. I can see that this is probably because the VLOOKUP arguments are quotes rather than dynamic cell references, but I'm not sure how to get around it. Any ideas?

Thanks.

[mikerickson]

I use R1C1 notation when putting formulas in cells via VBA. Try

With ActiveCell.Offset(0,7)
    .FormulaR1C1 = "=VLOOKUP(" & .Parent.Range("C7").Address(false, true, xlR1C1, False, .Cells) & ", R1C3:R99C5, 3, False)"
End With

[Insomniac53]

Thanks Mikerickson.

I'm going to have to study what you've sent me and get back tomorrow. It's advanced VBA code, at least for me, so I need to work out what it's doing. If there are any simpler solutions out there, I'd be interested to see them.

[mikerickson]

Once you understand R1C1 notation (in some senses, its easier than A1 notation), the tricky part is the RelativeTo argument of the .Address property.

[Insomniac53]

Using the R1C! you suggested, I did this, which seems to work:

ActiveCell.Offset(0, 7).FormulaR1C1 = "=VLOOKUP(RC3,Lists!R1C3:R99C5,3,FALSE)"

I've looked up some of the elements you used in your answer, like ".address" but can't find good explanations. Anyway, you pointed me in the right direction so much appreciated and rated. Thanks.