Using defined names with range.formula property

[DaveBlakeMAAT]

Hi all

I currently have a formula as listed below within my VBA script which all works great, however, when it populates the formula into the cell it inserts the underlying value of the named range instead of referencing the named range. The named range "MinimumIncomeThreshold" is pointing to an individual cell.

This means that the formula is not dynamic!

Range("M3", LastFormulaCell).Formula = "=IF(B3<" & [COLOR="#FF0000"]Range("MinimumIncomeThreshold")[/COLOR] & ",""Below Threshold"",MATCH(A3,list!$A$2:$A$1048576,0)+1)"

Does anyone know how I can correct this?

ps: I do not want to reference the cell address directly in my VBA code as this will break if someone decides to be "helpful" and insert new rows!!

Regards

Dave

 

[Norie]

Dave

Try this.

Range("M3", LastFormulaCell).Formula = "=IF(B3<MinimumIncomeThreshold ,""Below Threshold"",MATCH(A3,list!$A$2:$A$1048576,0)+1)"

 

[DaveBlakeMAAT]

Norie

Thanks for your reply however you appear to of accidentally chopped off the end of the formula!

 

[Norie]

Ach, that's the board doing that.

I'll try again.

Range("M3", LastFormulaCell).Formula = "=IF(B3 < MinimumIncomeThreshold ,""Below Threshold"",MATCH(A3,list!$A$2:$A$1048576,0)+1)"

 

[DaveBlakeMAAT]

Thanks Norie

I am such a muppet at times!!!!

I did try your suggestion originally, but stupidly I was manually changing the wrong cell value when testing.

I appreciate your help.

Regards

Dave