Macro Help

[510 Financeguy]

Hello all,

First off, let me just say that I am a non-programmer and do not know
anything about VB, but am hoping that someone here can help me out. I have a
formula in cell A75 and the cell references in the formula have to be changed
once a month, thus I would like to have a macro I can run each time the cell
references need to be changed.

An example of the formula is as follows:

Formula = SUM(D3,E4,F5)/SUM(C3,D4,E5)

Upon running the macro, the formula would change as follows:

Formula = SUM(E4,F5,G6)/SUM(D4,E5,F6)

and running the macro again, the formula would change to:

Formula = SUM(F5,G6,H7)/SUM(E5,F6,G7)


Much thanks in advance to anyone who can provide me with a solution to this.

 

[Rick Rothstein]

How close to reality is that "example" formula? What you are asking could be
difficult to implement if you are looking for a generalized solution as
opposed to a solution for a given set formula (I'm thinking of situations
with single cell versus multiple cell references at the moment). Also, is
the progression you showed (each reference has its row and column
incremented by one) exactly what you want to have done?

 

[510 Financeguy]

The "example" formula is the exact formula I am using, as is the progression
(in which each cell reference has its row and column incremented by one).

 

[Mike H]

Hi,

I'm sure I've made hard work of this and someone is going to post an elegant
solution but this works. If B76 and B77 are empty then the first and last 2
lines aren't necessary.


Sub IncrementIt()
myb76 = Range("B76").Formula
myb77 = Range("B77").Formula
Range("A76").AutoFill Destination:=Range("A76:B76")
Range("B76").AutoFill Destination:=Range("B76:B77")
Range("A76") = "'" & Range("B77").Formula
Range("A76").Formula = Range("A76").Formula
Range("B76").Formula = myb76
Range("B77").Formula = myb77
End Sub


Mike

 

[Rick Rothstein]

Give this macro a try (change the worksheet name assigned to the SheetName
constant to the name of your actual worksheet)...

Sub IncreaseReferences()
Dim F As String
Dim OldCells1 As String
Dim OldCells2 As String
Dim NewCells1 As String
Dim NewCells2 As String
Dim Parts() As String
Const SheetName As String = "Sheet1"
F = Worksheets(SheetName).Range("A75").Formula
Parts = Split(F, "(")
OldCells1 = Split(Parts(1), ")")(0)
OldCells2 = Split(Parts(2), ")")(0)
NewCells1 = Range(OldCells1).Offset(1, 1).Address(0, 0)
NewCells2 = Range(OldCells2).Offset(1, 1).Address(0, 0)
Worksheets(SheetName).Range("A75").Formula = Replace(Replace( _
F, OldCells1, NewCells1, , 1), OldCells2, NewCells2)
End Sub

 

[510 Financeguy]

Rick,

This worked perfectly. Thank you again for your assistance.