SUMIF in VBA

[Carl Clements]

Hi,

Is it possible to code a formula such as a SUMIF in VBA to look in a column from cell T2 until the row number previously counted, rather than selecting the whole column?

The reason I'm not selecting the column only is because I have merged cells in row 1 above

I currently have the formula below:

SUMIF(Database!S2:S63,'LODH OTC Recon'!C9,Database!Y2:Y63)

but would like to replace the S63 and Y63 with number of rows counted and stored as the variable 'CountROWS'

I thought the following code would work but unfortunately not

SUMIF(Database!T2:T & CountROWS,'LODH OTC Recon'!RC[-5],Database!T2:T & CountROWS))

Thanks,
Carl

 

[VoG]

Try like this

Range("A1").Formula = "SUMIF(Database!S2:" & countrows & ",'LODH OTC Recon'!C9,Database!Y2:Y" & countrows & ")"

 

[Richard Schollar]

Hi Carl

DO you want the range to extend from T2 to the row above where the formula is entered? R1C1 style refs is ideal for this:

Range("X2:X1000").FormulaR1C1 = "=SUMIF(Database!R2C20:R[-1]C20,'LODH OTC Recon'!RC[-5],Database!R2C20:R[-1]C20)"

Note that I wasn't sure about your second formula as it referenced the same range in both 1st and third arguments, but I have left it as is.