Help to create a macro to change a formula

ranvan · Sep 12, 2011

Hello,

I previously asked this question, but I think I may have not explained myself properly. What I am looking for is to create a macro that will allow me with one key stroke to change a formula in various places within one workbook. Below are 2 formulas. The first one is the original formula, the second one is the desired result after the macros is run. All I want is for the formula to move the equation down a row, and remain in the same cell, and not actually move the formula to another location. So basically do the sum for another 6 rows.

The formula is in Cell : F8 and should remain in cell : F8 after the macro is run.

[FONT=&quot]=SUM('Chart Data - CBV'!D31:D36)

I want it to become:

=SUM('Chart Data - CBV'!D32:D37)[/FONT]

HalfAce · Sep 12, 2011

Well... to change the formula in a cell - (one time) - you would just use something like this:

Code:

Range("F8").Formula = "=SUM('Chart Data - CBV'!D32:D37)"

However, I get the impression this doesn't get you all the way where you want to go with it. . .

ranvan · Sep 12, 2011

Thank you, but you are right, I want to create a macro that anytime I run it, it will move the formula equation down a cell. And this for the moment at 12 different places within my workbook. Eventually it would become more.

ranvan · Oct 12, 2011

So any thoughts on this questions? Still stuck on how to do this!

StephenGKorea · Oct 12, 2011

The code is not optimized so it is readable. There might be another way in doing it, but this way works for me, and appears to be what you want.

Code:

Sub ChangeIt()
    Dim celldata As String ' Formula in the original cell
    Dim FirstCell As Integer ' number of the First Cell
    Dim LastCell As Integer ' number of the second cell
    Dim MiddleI As Integer ' location of where the : is within celldata
    Dim StartI As Integer ' Location of the first digit of the first cell reference
    Dim EndI As Integer ' Location of the first digit of the second reference
    Dim tempI As Integer ' temp looping variable
    Dim txtTemp As String ' temp text variable
    On Error GoTo theend
    celldata = ActiveCell.Formula ' Load the variable celldata with the current cell formula
    MiddleI = InStr(celldata, ":") ' find the cell seperator ":"
' Find the first number of the first cell reference
    StartI = MiddleI
    Do Until txtTemp = "Stop"
        StartI = StartI - 1
        If IsNumeric(Mid(celldata, StartI, 1)) Then
        Else
            txtTemp = "Stop"
            StartI = StartI + 1
        End If
    Loop
    FirstCell = Mid(celldata, StartI, MiddleI - StartI) ' extract the number
    celldata = Replace(celldata, FirstCell, FirstCell + 1) ' replace the number with the increment of 1
    txtTemp = ""
' Find the first number of the 2nd cell reference
    EndI = InStr(MiddleI, celldata, ")")
    Do Until txtTemp = "Stop"
        EndI = EndI - 1
        If IsNumeric(Mid(celldata, EndI, 1)) Then
        Else
            txtTemp = "Stop"
            EndI = EndI + 1
        End If
    Loop
    LastCell = Mid(celldata, EndI, InStr(MiddleI, celldata, ")") - EndI) ' extract the number
    ActiveCell.Formula = Replace(celldata, LastCell, LastCell + 1) ' replace the number with an increment of 1 and re-write the formula in the active cell
theend:
End Sub

ranvan · Oct 12, 2011

Thanks so much. I am still new at all of this so if you would be so kind as to guide me as to what I need to change it would be much appreciated.

Thanks again!

StephenGKorea · Oct 13, 2011

Make sure your developer tab in the ribbon is activated. (If not, click on the office button --> Excel Options --> Popular from the left hand side --> Click on Show Developer tab in the ribbon)

Click on the Developer Tab ---> Marcos Button

Type Changeit under the Macro Name and click Create

cut and paste the code into this page. (Make sure that the Sub Changeit() and End Sub is listed only once, so to be safe, just highlight those lines and deleted them before you cut and paste the above published code)

Once you are done, Close the Visual Basic Window.

Back on your excel sheet, click the Macros button again and you should see Changeit on this list. Highlight it on the list and Click Options.

In the middle you can select your Shortcut Key. This is the key-combination you will use to activate the macro; Click OK, Click on the little X to close the Macro Box.

Enjoy.

ranvan · Oct 13, 2011

Thank you!!! Is there a way to modify this macro to automatically change the formulas? Meaning in the code itself reference the cells that I need changed automatically?

obiron · Oct 13, 2011

A couple of alternatives.

1) instead of using cells(x,y).formula you could use cells(x,y).formular1c1. this will give you the same but the cell references will be defined by their row and column offsets from the current cell rather than the column letter and row number

2) instead of making the forumula = sum(D32:D72), use a named range for D32:D37 then make the formula = sum(myNamedRange) and use a macro to dynamically change the cells that are in the named range. - This will be a better result if you are referring to the range in multiple places.

StephenGKorea · Oct 13, 2011

ranvan said:
Thank you!!! Is there a way to modify this macro to automatically change the formulas? Meaning in the code itself reference the cells that I need changed automatically?

Can you further explain? Maybe give an example? I am not sure what you are getting at..

Help to create a macro to change a formula

ranvan

New Member

Excel Facts

HalfAce

MrExcel MVP

ranvan

New Member

ranvan

New Member

StephenGKorea

New Member

ranvan

New Member

StephenGKorea

New Member

ranvan

New Member

obiron

Active Member

StephenGKorea

New Member

Similar threads

Forum statistics

Share this page

Help to create a macro to change a formula

New Member

Excel Facts

MrExcel MVP

New Member

New Member

New Member

New Member

New Member

New Member

Active Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock