Cell Referencing with a macro

[mchong]

Hi,

I've got a macro that needs some tweaking. Because the raw data report doesn't have a fixed number of columns, I am having trouble with cell referencing. My code below doesn't work because the cell "BX9" may not always be the correct cell to reference. Can someone tell me how I can get the macro to reference the 1st cell under the header "CODE" instead?


ActiveCell.FormulaR1C1 = "CODE"
Range("BX9").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-3]=""OCF"",""FREIGHT"",IF(RC[-3]=""SFB"",""FREIGHT"",IF(RC[-3]=""SFC"",""FREIGHT"",IF(RC[-3]=""SWE"",""FREIGHT"",IF(RC[-3]=""BAF"",""BAF"","""")))))"


Thanks in advance.

Marco

 

[Weaver]

Would this be what you're after, or is there more to it?

with activecell
.FormulaR1C1 = "CODE"
offset(1,0).FormulaR1C1 = _
"=IF(RC[-3]=""OCF"",""FREIGHT"",IF(RC[-3]=""SFB"",""FREIGHT"",IF(RC[-3]=""SFC"",""FREIGHT"",IF(RC[-3]=""SWE"",""FREIGHT"",IF(RC[-3]=""BAF"",""BAF"","""")))))"
end with

 

[mchong]

Hi excelR8R,

Tried to copy your code below into my macro but what I got when running it was "Compile Errror: sub of function not defined". Here's my code, can you tell me what I got wrong?

Range("B13Table[[#Headers],[Charge Currency]]").Select
ActiveCell.Offset(1, 0).EntireColumn.Insert
Range("B13Table[[#Headers],[Column1]]").Select
With ActiveCell
.FormulaR1C1 = "CODE"
Offset(1, 0).FormulaR1C1 = _
"=IF(RC[-3]=""OCF"",""FREIGHT"",IF(RC[-3]=""SFB"",""FREIGHT"",IF(RC[-3]=""SFC"",""FREIGHT"",IF(RC[-3]=""SWE"",""FREIGHT"",IF(RC[-3]=""BAF"",""BAF"","""")))))"
End With

Thanks.