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


--
__________________________________
HTH

Bob

"Bam" <(E-Mail Removed)> wrote in message
news:332F3B4E-E271-4576-A25E-(E-Mail Removed)...
> 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..