2

I recently developed a model for work and I am getting requests for adding additional features.

The problem isn't making the additions as most simply involve adding one or two additional columns. However, each time I add a column I must go through my code line by line and change every formula reference e.g. G to H, H to I, I to J etc.

Is there any way for me to automate this process without having to change each formula individually or a best practice that I can keep in mind when building my next model from scratch?

For example, let's say someone asks for an additional calculation that requires I insert a formula into column D. Currently I am going to each column (H, J, K, and L in the example below), and manually updating them to I, K, L, and M, respectively. 

'Year 1 ASP formula = (Year 1 Bookings) / (Year 1 Units)
FormulaRange_ASP1 = "H23:H" & NumRows
Range(FormulaRange_ASP1).Value = "=IFERROR(G23/F23,0)"
Range(FormulaRange_ASP1).NumberFormat = "0.00"
Application.ScreenUpdating = False

'Year 2 AR Units formula = (Year 1 Units) * (Vlookup AR Retention Rate for product in column C)
FormulaRange_ARUnits2 = "J23:J" & NumRows
Range(FormulaRange_ARUnits2).Value = "=IF(E23 = 12,VLOOKUP(D23,$B$8:$F$19,4,FALSE),0) * F23 * AR_Opt_In"
Range(FormulaRange_ARUnits2).NumberFormat = "#,##0_)"
Application.ScreenUpdating = False

'Year 2 AR ASP formula = (Vlookup ASP for product in column C)
FormulaRange_AR_ASP2 = "K23:K" & NumRows
Range(FormulaRange_AR_ASP2).Value = "=IFERROR(VLOOKUP(D23,$B$8:$F$19,2,FALSE),0)"
Range(FormulaRange_AR_ASP2).NumberFormat = "0.00"
Application.ScreenUpdating = False

'Year 2 AR Bookings formula = (Year 2 Units) * (Year 2 ASP)
FormulaRange_AR_Bookings2 = "L23:L" & NumRows
Range(FormulaRange_AR_Bookings2).Value = "=J23 * K23"
Range(FormulaRange_AR_Bookings2).NumberFormat = "$#,##0_)"
Application.ScreenUpdating = False
Community
  • 1
  • 1
sirish59
  • 53
  • 1
  • 8
  • Can you show us some of your code please? – Alex P Jan 08 '14 at 17:37
  • 3
    I'm wondering why you need to create all these formulas in code. If you just created them in a worksheet (perhaps a template?) the formulas would adjust automatically when columns are added or deleted. – Doug Glancy Jan 08 '14 at 18:11

2 Answers2

2

What I usually do is add some constants to the beginning of the module (or a separate module, for bigger projects) with column number references.

Const c_Column1 As Integer = 1
Const c_Column2 As Integer = 2

So, throughout the code, you can do something like:

For i = 2 to 10
   ActiveSheet.Cells(i, c_Column1).Value = i
Next i

.. and never have to worry about changing your code, only the constants.

Notice the use of Cells() instead of Range(), because it allows referencing the columns as numbers. To reference a range, you'll have to use the following sintax:

With ActiveSheet
    For i = 2 to 10
        .Range(.Cells(i, c_Column1), .Cells(i, c_Column2)).Value = i
    Next i
End With

You can also declare the constants as String and assign them column letters instead of numbers. It may be easier to reference ranges, but you'll have to use string concatenation everywhere.

Fabio Pereira
  • 338
  • 1
  • 8
1

One possibility is to use relative R1C1 references.

E.g. instead of specifying a formula in H23 as:

Range(FormulaRange_ASP1).Value = "=IFERROR(G23/F23,0)"

You can specify it as:

Range(FormulaRange_ASP1).FormulaR1C1 = "=IFERROR(R[0]C[-1]/R[0]C[-2],0)"
Joe
  • 122,218
  • 32
  • 205
  • 338