Columns Reference in VBA after inserting a new column
 

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..

Columns Reference in VBA after inserting a new column
 

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"")))"


--
__________________________________
HTH

Bob

"Bam" wrote in message
...
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..