Generate a formula from VBA

py1ro

Board Regular
Joined
Sep 30, 2005
Messages
70
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
hallo, py1ro,
(you must love greek !?)

try something like this
Code:
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

Board Regular
Joined
Sep 30, 2005
Messages
70
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

Board Regular
Joined
Sep 30, 2005
Messages
70
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
 

Forum statistics

Threads
1,078,474
Messages
5,340,552
Members
399,383
Latest member
rahmanab001

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top