vlookup to dynamic row numbers and column alphas

[ajm]

hi all, i am using an If statement with a vlookup formula to return either the value in a particular cell, or, if the particular cell is blank, to then return a formula. The results of this will then be pasted as values to a new tab, effectively inserting the formula into the new tab. However, i need a dynamic formula specific to each row.

So, currently, where the result of the initial vlookup is blank, this formula is to be inserted:

"=IF(AND($AB34>AE$33,$AB34<=AF$33),$AV$2,IF(AND(AF$33>=$Z34,AF$33<=$AB34),$AV$3,""""))"


My first data row is Row34 and this formula is inserted at AF34. In row 35, these should read 35.

I had thought to use INDIRECT and but with 400k cells, thought it might slow the processing down too much. Any suggestions?

 

[sijpie]

The trick is to use R1C1 formula notation. In Excel go to File/Options and in Formulas tick the 'Use R1C1 notation' option.
Now go back to the worksheet and look at your formula. It now uses the R1C1 notation (R standing for Rows and C for columns, with the numbers being the relative offset). Copy this formula.

in your VBA set the range to the formula using:

Range("AF34:AF400000").FormulaR1C1="=IF(AND(RC28>R33C[-1],RC28<=R33C),R2C48,IF(AND(R33C>=RC26,R33C<=RC28),R3C48,""""))"

Now you can untick the R1C1 option again in excel, to get back to 'normal'.

This is a very powerful trick for this type of problem. In fact Excel started off with the R1C1 notation, because it is so flexible. But users wanted the Lotus123 notation, so Microsoft added that to Excel. But behind the scenes Excel uses R1C1 notation...

Just so you know.

 

[sijpie]

Just for a better understanding of the R1C1 notation:

=IF(AND($AB34>AE$33,$AB34<=AF$33),$AV$2,IF(AND(AF$33>=$Z34,AF$33<=$AB34),$AV$3,""""))
in R1C1 notation:
=IF(AND(RC28>R33C[-1],RC28<=R33C),R2C48,IF(AND(R33C>=RC26,R33C<=RC28),R3C48,""""))

your formula sits in AF34. So row 34 has no offset , Neither does column AF have an offset.

$AB34 is a cell on the same row, with a fixed column ($AB) so that is column 28. So from AF34 you refer to $AB34 as: RC28. (actually R[0]C28, with the [] giving the offset, ie relative position)

AE$33 is a cell with a fixed row, one column to the left. So from AF34 that is R33C[-1]

 

[ajm]

hmm, interesting. will give it a crack. thanks.