Columns Reference in VBA after inserting a new column

[Bam]

Hi All,

I have a workbook which seems to be ever evolving, and means i often add in
extra columns which throws out the formulas i have within.
Eg: I'm using formulas like this.

ActiveSheet.Range(Cells(4, 7), Cells(myrowcount, 6)).Formula =
"=IF(S4<>"""",""ORD"",IF(L4>(J4+K4),""Yes"",IF(H4<=8,""Yes"",""No"")))"

Each time I add in a new column, it means i have to redo each formula I have.

What's the best way to handle this??

My Column headings don't change, so could I use a "Match("ColumnHeader" type
statement???

Any help would be appreciated.

Thanks..

 

[Bob Phillips]

Use defined names to name the columns. So say you name cell L1 as colL. J1
as colJ, K1 as colK, H1 as colH, S1 as colS, then you would use

ActiveSheet.Range(Cells(4, 7), Cells(myrowcount, 6)).FormulaR1C1 = "=IF(R4C"
& colS.column & "<>"""",""ORD"",IF(R4C" & colL.column & "4>(R4C" &
colJ.column & "+R4C" & colK.column & "),""Yes"",IF(R4C" & colH.column &
"<=8,""Yes"",""No"")))"