I did this very thing today, so thought I would share this tip with you as it saved me a lot of time. Say you wanted to have this formula go into column 8 of the range you created in VBA for the number of dynamic rows you extracted using Advanced Filter.
=CHOOSE(
OR(C6={“EL”,”IL”})*1+(C6=”MT”)*2+OR(C6={“IP”,”EP”})*3+(C6=”SC”)*4+(C6=”AD”)*5,
“Labour”,”Materials”,”Plant”,”SubContract”,”Overheads”)
There are two issues:
1) the relative formula needed and the RC[-6] type syntax and
2) the very particular VBA coding required for the above such as double quotes for each quote which can be time consuming to get right.
BTW I set up the range rTarget in VBA easily enough as I have a fixed start cell denied in a constant T_STARTCELL, know my end column defined in constant T_ENDCOL and then used this statement to find the Lastrow which is T_FinalRow
[vb]T_FinalRow = Sheet15.Cells(Cells.Rows.Count, T_CHKCOL).End(xlUp).Row
Set rTarget = Sheet15.Range(T_STARTCELL, T_ENDCOL & T_FinalRow)[/vb]
So I wanted the above relative formula to happen in the 8th column of rTarget.
Good old macro recorder to the rescue. On the worksheet with my formula as above that would be the first cell of the 8th column of this range (remembering I have a fixed start row) selected, I start the macro recorder then hit F2 for editing the formula and just go into the formula and ‘kind of’ pretend to change something and straight away change it back then hit stop macro. Then if I go to that recorded macro there is my formula converted to VBA code all relative and ready to use. Now it is long, but it works and I don’t want to push my luck so I didn’t split it in to several lines but have below just for blog posting purposes but in the VBE it’s one horrible line.
rTarget.Columns(8).Formula = "=CHOOSE(" & Chr(10) & "OR(RC[-6]={""EL"",""IL""})*1+(RC[-6]=""MT"")*2 +OR(RC[-6]={""IP"",""EP""})*3+(RC[-6]=""SC"")*4+(RC[-6]=""AD"")*5," & Chr(10) & """Labour"",""Materials"", ""Plant"",""SubContract"",""Overheads"")"
Now that trick is the difference between getting this right easily or just doing it another way. I couldn’t write that code in VBA quickly for this non trivial formula. For a long time my practical self taught way of entering a relative formula in VBA, was to copy the formula stored in a cell (usually as a named range like FxTemplate1) which I don’t see as a significantly inferior way either – like all alternatives it has pros and cons. Pros of being easier to see and understand and update, but one con is if you needed to copy a lot of these in one sub, it will be slower in VBA than directly entering the formula into the sub. Each time VBA has to access the worksheet to get data slows VBA’s efficiency.