I've been struggling with this for a little while now, and I can't find Tim who usually solves my problems (
), and the internet in general is being useless.
I have a super-clever macro which (amongst other things) pastes formulae into cells. What I want to do is modify the formula below, so that when the macro pastes it in the cell, it retains the cell references in the formula:
"=IF(ISNA(VLOOKUP(CONCATENATE(" & """G""" & "," & FormulaCell.Offset(0, -20) & "),'[communicationsheet.xls]IA - NB'!A$7:BV$252,74,FALSE))," & """Input estimate""" & ", VLOOKUP(CONCATENATE(" & """G""" & "," & FormulaCell.Offset(0, -20) & "),'[communicationsheet.xls]IA - NB'!A$7:BV$252,74,FALSE))" At the moment it gives "...(CONCATENATE("G",44456)..."* - I want it to return "(CONCATENATE("G",C4)" (or C5, or C6, and so on. Column C, row-that-I-am-in. You know what I mean).
I believe this can be done using R1C1 cell references (I think that's the term for them), but I don't know how to use them, and every time I Google, all I find is loads of clever-clogs saying "Use "offset" - it's much simpler". And I've asked everyone in my group who can write VBA, and they all said "R1C1? No, I prefer "offset". Why don't you use that?"
*weeps*
So, non-offset-related solutions gratefully accepted, ta.
*44456 being the value in cell C4
