asked on Macro to update formula of columns in worksheet
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)/100
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.
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)/100
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.
Microsoft ExcelVisual Basic Classic
Last Comment
Glenn Ray
Same principle for Sheet2
Find :W
Replace :X
Click Replace All
Thanks
Rob
Find :W
Replace :X
Click Replace All
Thanks
Rob
ASKER
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 ?
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 ?
Before I suggest a VBA solution, what do you mean by "formula update EXCLUDE those shaded cells within the column" ?
ASKER
"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.
For column U, update whole of the column.
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
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
For example, column S:
=(OFFSET(Sheet2!$A4,0,$S$1
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
Thanks
Rob H
=(OFFSET(Sheet2!$A4,0,$S$1
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
Thanks
Rob H
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
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
To adjust the SUM on sheet2 it would be:
=SUM(OFFSET($O4,0,0,1,$J$1
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
This macro will update all your formulas on the two sheets:
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
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
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
ASKER
Hi glenn thanks for the macro.
all ok except for column U.
in my comments in
https://www.experts-exchange.com/questions/28552102/Macro-to-update-formula-of-columns-in-worksheet.html?anchorAnswerId=40427584#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.
all ok except for column U.
in my comments in
https://www.experts-exchange.com/questions/28552102/Macro-to-update-formula-of-columns-in-worksheet.html?anchorAnswerId=40427584#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.
Ceneiqe - have you looked at the OFFSET formula option?
One downside with VBA is that you lose the undo history.
One downside with VBA is that you lose the undo history.
ASKER
Hi Rob, sorry offset formula doesn't work for me.
i don't need the undo history since file will be saved monthly.
i don't need the undo history since file will be saved monthly.
I'm sorry that I missed that instruction about column U. Here is the updated code:
-Glenn
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
-Glenn
ASKER
There is a compile error:
End if without block if
highlighted at "Sub Update_Formulas()"
End if without block if
highlighted at "Sub Update_Formulas()"
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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