Columns Reference in VBA after inserting a new column

  • Thread starter Thread starter Bam
  • Start date Start date
B

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..
 
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"")))"
 
Back
Top