Solved

Macro to update formula of columns in worksheet

Posted on 2014-11-06
15
107 Views
Last Modified: 2014-11-13
Macro to update formula in column in active worksheet.
For example if column J has formula "=SUM(O4:W4)" then macro should update it to "=SUM(O4:X4)"

Worksheet 1 "sheet1":
To Update:
1. column S - =((Sheet2!W4)*$A4)/1000 > update to  =((Sheet2!X4)*$A4)/1000
2. column T- =((AF4-Sheet2!W4)*$A4)/1000 > update to =((AF4-Sheet2!X4)*$A4)/1000
3. column U- =AF4/AE4-1 > update to =AG4/AF4-1
4. column V- =((AF4-AE4)*$A4)/1000 > update to =((AG4-AF4)*$A4)/1000

formula update EXCLUDE those shaded cells within the column.

Worksheet 2 "Sheet2":
To Update:
1. column J -  "=SUM(O4:W4)" then macro should update it to "=SUM(O4:X4)"

formula update up to row 130 ONLY

notice that the formula is always an increment of the column.
0
Comment
Question by:ceneiqe
  • 6
  • 5
  • 4
15 Comments
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40426375
You don't necessarily need a Macro:

Highlight required areas of S & T and use Edit Replace:
Find          !W
Replace    !X
Click Replace All

U & V not quite so simple but doable with same principle:

Do two Edit Replaces:
Find           (AF
Replace     (AG
Click Replace All

Find           (AE
Replace     (AF
Click Replace All

Important to do in correct order for U & V.

Alternatively, if W is empty, copy cells from V and paste into W and then CUT from W back into V. This may have an impact elsewhere on formulas referring to V. If W is not empty insert column at W and then delete column after Copy/Cut actions.

Thanks
Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40426384
Same principle for Sheet2

Find           :W
Replace     :X
Click Replace All

Thanks
Rob
0
 

Author Comment

by:ceneiqe
ID: 40426565
thanks Rob for the suggestion.
but i am tired of doing all these find, replace, copy and paste. it is too time consuming as i have other worksheets to compile too. i need to save time for analysis instead of wasting time on the data entry work.

can anyone help ?
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40426817
Before I suggest a VBA solution, what do you mean by "formula update EXCLUDE those shaded cells within the column" ?
0
 

Author Comment

by:ceneiqe
ID: 40427584
"formula update EXCLUDE those shaded cells within the column"  = if there are cells that are highlighted with a colour then DO NOT update the formula and this applies to only Column S, T and V in Sheet1.

For column U, update whole of the column.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40428104
If you were to upload a sample file, there may be a different way to do this rather than having to adjust the formulas each time.

For example, you may be able to use the OFFSET function to specify particular columns, you would then just need to change a value in one cell.

Thanks
Rob H
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40428125
For example, column S:

=(OFFSET(Sheet2!$A4,0,$S$1,1,1)*$A4)/1000

Assuming value 22 in S1, this will use the value in Sheet2!W4. Increase S1 to 23 and it will use X4.

Not knowing the scenario, I have assumed OFFSET from col A, col W is 22 cols to the right. You can make the value in S1 more relevant if so required. For example, assume sheet2 was a finance report and col W referred to period 9, therefore assume col O would be period 1. Above formula could be:

=(OFFSET(Sheet2!$O4,0,$S$1-1,1,1)*$A4)/1000  and value of S1 would be 9 to represent period 9 reporting.

Thanks
Rob H
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 31

Expert Comment

by:Rob Henson
ID: 40428260
Bizarre coincidence that I chose col O when I then notice that the sum on sheet2 starts at col O.

To adjust the SUM on sheet2 it would be:

=SUM(OFFSET($O4,0,0,1,$J$1))   where J1 contains number of columns required.

See attached with one example for each of the columns S to V using OFFSET and in col J on sheet 2. One change to S1 on sheet1 will change all formulas.

Thanks
Rob H
OFFSET-to-adjust-formula.xlsx
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40428769
This macro will update all your formulas on the two sheets:
Option Explicit
Sub Fix_Formulas()
    Dim r As Integer
    Dim lngLR As Long
    
    lngLR = Cells.SpecialCells(xlLastCell).Row
    
    Sheets("Sheet1").Select
    For r = 4 To lngLR
        If Range("S" & r).Interior.Pattern = xlNone Then
            Range("S" & r).Formula = "=(Sheet2!X" & r & "*$A" & r & ")/1000"
            Range("T" & r).Formula = "=((AF" & r & "-Sheet2!X" & r & ")*$A" & r & ")/1000"
            Range("U" & r).Formula = "=AG" & r & "/AF" & r & "-1"
            Range("V" & r).Formula = "=((AG" & r & "-AF" & r & ")*$A" & r & ")/1000"
            'or, using relative referencing
            'Cells(r, 19).FormulaR1C1 = "=(Sheet2!RC[5]*$RC1)/1000"
            'Cells(r, 20).FormulaR1C1 = "=((RC[12]-Sheet2!RC[5])*RC1)/1000"
            'Cells(r, 21).FormulaR1C1 = "=RC[12]/RC[11]-1"
            'Cells(r, 22).FormulaR1C1 = "=((=RC[12]-RC[11])*RC1)/1000"
        End If
    Next r
    
    Sheets("Sheet2").Select
    For r = 4 To 130
        Range("J" & r).Formula = "=SUM(O" & r & ":X" & r & ")"
        'or, using relative referencing
        'Cells(4, 10).FormulaR1C1 = "=SUM(RC[5]:RC[14]"
    Next r
    
End Sub

Open in new window


On Sheet1, it will update all the formulas where there is no shading (.Interior.Pattern = xlNone).  On Sheet2, it updates all cells from row 4 to 130.  NOTE:  It is only checking for shading in column S; the assumption being that if S is shaded, the entire row is also.  Having not seen your workbook, this is where I started.

There are alternate blocks of code that are commented out that show the same formulas, but using R1C1 method to create them.  They are more concise, but harder to interpret.  However, I included them for your reference.

Regards,
-Glenn
0
 

Author Comment

by:ceneiqe
ID: 40430041
Hi glenn thanks for the macro.

all ok except for column U.

in my comments in
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28552102.html#a40427584

i mentioned "For column U, update whole of the column. "
which means for column U, just update the formula regardless whether the cell is highlighted or not.

Thanks.
0
 
LVL 31

Expert Comment

by:Rob Henson
ID: 40430101
Ceneiqe - have you looked at the OFFSET formula option?

One downside with VBA is that you lose the undo history.
0
 

Author Comment

by:ceneiqe
ID: 40430767
Hi Rob, sorry offset formula doesn't work for me.
i don't need the undo history since file will be saved monthly.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40431414
I'm sorry that I missed that instruction about column U.  Here is the updated code:
Option Explicit
Sub Fix_Formulas()
    Dim r As Integer
    Dim lngLR As Long
    
    lngLR = Cells.SpecialCells(xlLastCell).Row
    
    Sheets("Sheet1").Select
    For r = 4 To lngLR
        If Range("S" & r).Interior.Pattern = xlNone Then
            Range("S" & r).Formula = "=(Sheet2!X" & r & "*$A" & r & ")/1000"
        End If
        If Range("T" & r).Interior.Pattern = xlNone Then
            Range("T" & r).Formula = "=((AF" & r & "-Sheet2!X" & r & ")*$A" & r & ")/1000"
        End If
        If Range("V" & r).Interior.Pattern = xlNone Then
            Range("V" & r).Formula = "=((AG" & r & "-AF" & r & ")*$A" & r & ")/1000"
        End If
        Range("U" & r).Formula = "=AG" & r & "/AF" & r & "-1"
        End If
    Next r
    
    Sheets("Sheet2").Select
    For r = 4 To 130
        Range("J" & r).Formula = "=SUM(O" & r & ":X" & r & ")"
    Next r
End Sub

Open in new window


-Glenn
0
 

Author Comment

by:ceneiqe
ID: 40431953
There is a compile error:

End if without block if

highlighted at "Sub Update_Formulas()"
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40433487
Oops..delete line 20.  Revised:

Option Explicit
Sub Fix_Formulas()
    Dim r As Integer
    Dim lngLR As Long
    
    lngLR = Cells.SpecialCells(xlLastCell).Row
    
    Sheets("Sheet1").Select
    For r = 4 To lngLR
        If Range("S" & r).Interior.Pattern = xlNone Then
            Range("S" & r).Formula = "=(Sheet2!X" & r & "*$A" & r & ")/1000"
        End If
        If Range("T" & r).Interior.Pattern = xlNone Then
            Range("T" & r).Formula = "=((AF" & r & "-Sheet2!X" & r & ")*$A" & r & ")/1000"
        End If
        If Range("V" & r).Interior.Pattern = xlNone Then
            Range("V" & r).Formula = "=((AG" & r & "-AF" & r & ")*$A" & r & ")/1000"
        End If
        Range("U" & r).Formula = "=AG" & r & "/AF" & r & "-1"
    Next r
    
    Sheets("Sheet2").Select
    For r = 4 To 130
        Range("J" & r).Formula = "=SUM(O" & r & ":X" & r & ")"
    Next r
End Sub

Open in new window

0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now