Generate a formula from VBA

[py1ro]

I'm having difficulties finding a way to create the following type of formula:

=sumif("A" & begin, "A" & end; "A" & end +2; "B" & begin, "B" & end)

I tried FORMULA= instead of FORMULAR1C1= but cannot get beyond the quotation marks.

Is it possible at all? I found no examples.

Thanks

 

[erik.van.geit]

hallo, py1ro,
(you must love greek !?)

try something like this

Sub Test()
FR = 1
LR = 5

    Range("A10").FormulaR1C1 = "=SUMIF(R" & FR & "C1:R" & LR & "C1,R" & LR + 2 & "C1,R" & FR & "C2:R" & LR & "C2)"

End Sub

how I found this ?
just asked my best friend: the macro recorder
then carefully replaced some parts

two examples
Range("A1")
Range("A" & FR)

"=R1C1"
"=R" & FR & "C1"

kind regards,
Erik

 

[py1ro]

Hi Eric, thanks for the FAST reply! I will play with this tmw. It´s midnite here in Rio de Janeiro and I was just going to bed. Greek? If you refer to my "name", it is my hamradio callsign!!!

Best regards
Rolf, my real name!

 

[py1ro]

Hi Eric

It actually worked! Thanks indeed for bringing me much closer to understanding R1C1 !

Hartelijke groeten van Rio! (sp may be wrong)
(I lived 3 years in Indonesia in a past life...)
Rolf