Sum range using vba

[PSG]

I have the following code, which was working fine until now. The problems which I am facing is due to the change in condition. Now I'm required to add a column between D & E due to which my formula (Fm1 should change from "D7-E7" to "D7-F7").

Need you help to automate this, the code should hold true no matter how many columns are added between D & E.

Thanks in advance for your help!!

Dim Fm1 As String
Fm1 = "D7-E7"
    ActiveSheet.Rows(6).Find("Forecast").Select
    ActiveCell.Offset(1, 0).Formula = "=" & Fm1
    ActiveCell.Offset(1, 0).Copy
    Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(7, 0)).PasteSpecial xlPasteFormulas

 

[Smallman]

PSG

Why do you need VBA? If you just put some formula under the forcast column you can add as many columns as you wish and the formula will adjust perfectly.

If VBA is a must what is unique about the columns you want to add the formula to. You could use the same technique as finding the forecast.

Take care

Smallman

 

[PSG]

Hi Smallman,

I need VBA because, the above code is a part of big program used for calculating various forecast and pipeline. Hence the formula doesn't work as the program itself insert the column between D & E.
Now since I don't know how to tweak the code, need help in this regard.

 

[KenU]

@PSG,

Without knowing the layout of the worksheet and the criteria for adding columns (when, how many, etc.), it will be difficult to provide a reliable VBA solution.

One possibility would be to define a named range in the Name Manager of the workbook. It should be defined to span the original columns D:E. When new columns are added between D and E, the reference defining the named range will be updated accordingly. The named range may then be referenced either in a formula or from VBA.

Hope that helps.

Regards,
Ken