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
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
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
If you are experiencing a similar issue, please ask a related question
Title | # Comments | Views | Activity |
---|---|---|---|
Excel Calculate Average - Grouped Values | 7 | 23 | |
Why do I often "Too many different cell formats" error message in Excel2010? | 2 | 26 | |
Excel Formula to check both condition's and return values | 2 | 29 | |
Copying from excel I am getting extra text | 11 | 43 |
Join the community of 500,000 technology professionals and ask your questions.