using xlup function within a formula to select your range

[jufglanville]

Can you use the xlup function in a vba formula? I am using the below formula, however I want to try and change the R[-52] to xlup and the R[-51] to xlup and offset one down, is this possible?

TaxCode4.FormulaR1C1 = "=IF(LOOKUP(2,1/(R[-52]C:R[-1]C<>"" ""),(R[-51]C[-1]:RC[-1]))="""",LOOKUP(2,1/(R[-52]C:R[-1]C<>"" ""),(R[-52]C[-1]:R[-1]C[-1])),LOOKUP(2,1/(R[-52]C:R[-1]C<>"" ""),(R[-51]C[-1]:RC[-1])))"

 

[Joe4]

Just do the last row calculation before this line, and use the variable in your formula (note that the variable needs to be outside the double-quotes, so you will need to combine the literal text part and variables with "&"s).
If you do that though, you will probably need to change your formula, as it is currently using R1C1 formula referencing