Problem usng variable within RC[-1]

[Ronanm]

Hi

Can I use a variable within the RC addressing in a formula in VBA?

Selection.Value = "=sumIF(RC[-1]:R[lastrow]C,RC[-1]:R[lastrow]C,RC[8]:R[lastrow]C)"

I would have thought that the formula above would be fine!!!

Thanks

Ronan

 

[VoG]

Try

Selection.FormulaR1C1 = "=sumIF(RC[-1]:R[" & lastrow & "]C,RC[-1]:R[" & lastrow & "]C,RC[8]:R[" & lastrow & "]C)"

 

[MrKowz]

Couple things:

Selection[B][COLOR=red].FormulaR1C1[/COLOR][/B] = "=sumIF(RC[-1]:R[[COLOR=red][B]" & lastrow & "[/B][/COLOR]]C,RC[-1]:R[[B][COLOR=red]" & lastrow & "[/COLOR][/B]]C,RC[8]:R[[COLOR=red][B]" & lastrow & "[/B][/COLOR]]C)"

Anothing thing to note is that the code the Macro Recorder gives you is extremely messy and can be cleaned up by removing most every "Select" statement. For example:

Range("A1").Select
Selection.Value = "Bob"

can be reduced to:

Range("A1") = "Bob"

Also, R1C1 formulas are incredibly hard to interpret. If you are able to translate it into an A1 style formula, you can use something like:

Range("B1").Formula = "=SUM(A1:A" & lastrow & ")"

 

[Ronanm]

Perfect thanks

Ronan

 

[Ronanm]

Actually... Below works, but one other issue. How do I make the Rows static, i.e. the $ signs? Do I need to &'sand them on as well?

Selection.FormulaR1C1 = "=sumIF(RC[-1]:R[" & lastrow & "]C[-1],RC[-1]:R[" & lastrow & "]C[-1],RC[9]:R[" & lastrow & "]C[9])"

Thanks

Ronan