Referencing Ranges in IF THEN Statement in VBA

[klugdav]

Hi,

I am trying to write an IF THEN statement that checks to see if a cell is less than certain cells on a different sheet. I got the macro to work with hard inputs, but I can't seem to figure out how to input the ranges I would like to input. Below is my formula:

ActiveCell.FormulaR1C1 = "=IF(RC[-1] < ?, 1, IF(RC[-1] < ?, 2, IF(RC[-1] < ?, 3, IF(RC[-1] < ?, 4, IF(RC[-1] < ?, 5,0)))))"

For each ? I want to put in a reference to a cell in a named range. The references are as follows:

Range("PREINPUTS_CHILLERINFO").Cells(1,3)
Range("PREINPUTS_CHILLERINFO").Cells(2,3)
Range("PREINPUTS_CHILLERINFO").Cells(3,3)
Range("PREINPUTS_CHILLERINFO").Cells(4,3)
Range("PREINPUTS_CHILLERINFO").Cells(5,3)

Each time I try to put in the reference it won't run. In the end I want to copy and paste this cell down into a whole range. Would it be easier to use the range formulas instead?

Thanks for the help.

 

[Jonmo1]

I'll do the first one, you should be able to infer how to do the rest..

ActiveCell.FormulaR1C1 = "=IF(RC[-1] < ?, 1, ....
would be
ActiveCell.FormulaR1C1 = "=IF(RC[-1] <" & Range("PREINPUTS_CHILLERINFO").Cells(1,3) & ", 1, ....


Or

Var1 = Range("PREINPUTS_CHILLERINFO").Cells(1,3)

ActiveCell.FormulaR1C1 = "=IF(RC[-1] < " & Var1 & ", 1, ....

 

[Andrew Poulsom]

Maybe:

ActiveCell.FormulaR1C1 = "=IF(RC[-1]<'PREINPUTS_CHILLERINFO'!R1C3,1,IF(RC[-1]<'PREINPUTS_CHILLERINFO'!R2C3,2,IF(RC[-1]<'PREINPUTS_CHILLERINFO'!R3C3,3,IF(RC[-1]<'PREINPUTS_CHILLERINFO'!R4C3,4,IF(RC[-1]<'PREINPUTS_CHILLERINFO'!R5C3,5,0)))))"

 

[Craigjre]

Hi,

I am trying to write an IF THEN statement that checks to see if a cell is less than certain cells on a different sheet. I got the macro to work with hard inputs, but I can't seem to figure out how to input the ranges I would like to input. Below is my formula:

ActiveCell.FormulaR1C1 = "=IF(RC[-1] < ?, 1, IF(RC[-1] < ?, 2, IF(RC[-1] < ?, 3, IF(RC[-1] < ?, 4, IF(RC[-1] < ?, 5,0)))))"

For each ? I want to put in a reference to a cell in a named range. The references are as follows:

Range("PREINPUTS_CHILLERINFO").Cells(1,3)
Range("PREINPUTS_CHILLERINFO").Cells(2,3)
Range("PREINPUTS_CHILLERINFO").Cells(3,3)
Range("PREINPUTS_CHILLERINFO").Cells(4,3)
Range("PREINPUTS_CHILLERINFO").Cells(5,3)

Each time I try to put in the reference it won't run. In the end I want to copy and paste this cell down into a whole range. Would it be easier to use the range formulas instead?

Thanks for the help.

Try:

ActiveCell.FormulaR1C1 = "=IF(RC[-1]<" &
Range("PREINPUTS_CHILLERINFO").Cells(1,3) &",1,IF(RC[-1]<" &
Range("PREINPUTS_CHILLERINFO").Cells(2,3) &",2,IF(RC[-1]<" &
Range("PREINPUTS_CHILLERINFO").Cells(3,3) &",3,IF(RC[-1]<" &
Range("PREINPUTS_CHILLERINFO").Cells(4,3) &",4,IF(RC[-1]<" &
Range("PREINPUTS_CHILLERINFO").Cells(5,3) & ", 5,0)))))"

 

[klugdav]

Thanks jonmo1, I used the second method and it worked. Part of my problem was that it was referencing a table that isn't necessarily filled out all the time. Once I entered in zeros for the missing data it worked fine.