I have an excel sheet which i need to replace the formula for column C and column H. Column C always has the form of 'Starts Report'!$C$13", and Column H has the form of "=SUM('Starts Report'!C6:C13)". The replacement for both columns is to replace the number "13" with (13-8). And this replacement should be done for every row of column C and column H.
I wrote the function for replacing the formula and it is used as part of another macro (both shown as below, the macro part code is just an example of how it is used)
My question is that for the 3rd if scenario, i.e., for column H and when the number to be replaced is a double digit, it doesn't turn out as what i hope. In this case, the original formula is: "=SUM('Starts Report'!C6:C13)" , but instead of returning C6:C5, it returned C5:C14. My guess is that the if looping is somehow not what i wanted. But i can't figure out why. Any help would be appreciated.
The Macro where the function is used (only part of the entire macro):
lastRowNumber = 20
For i = 11 To lastRowNumber
ReplaceFormula "C", i, -8
ReplaceFormula "H", i, -8
Next i
The function of replacing formula is:
Sub ReplaceFormula(ByVal column As String, ByVal i As Integer, ByVal j As Integer)
Dim searchString As String
Dim formulaNumber As Long
Dim toReplace As String
Dim r As Range
Set r = ActiveWorkbook.Worksheets("Monthly Dashboard").Range(column & i)
searchString = r.Formula
'the cell to be replaced always starts with a string "Starts"
If IsEmpty(searchString) = False And (InStr(searchString, "Starts") > 0 Then
'when the formula to be replaced has the form of "='Starts Report'!$C$13", so the number to be replaced is a double digit
If Right(searchString, 1) <> ")" And Left(Right(searchString, 2), 1) <> "$" Then
toReplace = Right(searchString, 2)
If Len(toReplace) > 0 Then
formulaNumber = CInt(toReplace) + j
ActiveWorkbook.Worksheets("Monthly Dashboard").Cells(i, column).Formula = Replace(searchString, CStr(toReplace), formulaNumber)
End If
'when the formula to be replaced has the form of "='Starts Report'!$C$9" and the number to be replaced is a single digit
ElseIf Right(searchString, 1) <> ")" And Left(Right(searchString, 2), 1) = "$" Then
toReplace = Right(searchString, 1)
If Len(toReplace) > 0 Then
formulaNumber = CInt(toReplace) + j
ActiveWorkbook.Worksheets("Monthly Dashboard").Cells(i, column).Formula = Replace(searchString, CStr(toReplace), formulaNumber)
End If
' this is where i had the trouble, instead of returning C6:C5, it returned C5:C14
'when the formula to be replaced has the form of "=SUM('Starts Report'!C6:C13)", so the number to be replaced is a double digit
ElseIf Right(searchString, 1) = ")" And IsNumeric(Left(Right(searchString, 3), 1)) = True Then
toReplace = Left(Right(searchString, 3), 2)
If Len(toReplace) > 0 Then
formulaNumber = CInt(toReplace) + j
ActiveWorkbook.Worksheets("Monthly Dashboard").Cells(i, column).Formula = Replace(searchString, (CStr(toReplace) & ")"), (formulaNumber & ")"))
End If
'when the formula to be replaced has the form of "=SUM('Starts Report'!I6:I9)", so the number to be replaced is a single digit
ElseIf Right(searchString, 1) = ")" And IsNumeric(Left(Right(searchString, 3), 1)) = False Then
toReplace = Left(Right(searchString, 2), 1)
If Len(toReplace) > 0 Then
formulaNumber = CInt(toReplace) + j
ActiveWorkbook.Worksheets("Monthly Dashboard").Cells(i, column).Formula = Replace(searchString, (CStr(toReplace) & ")"), (formulaNumber & ")"))
End If
End If
End If
End Sub
Aucun commentaire:
Enregistrer un commentaire