mardi 6 septembre 2016

VBA using IF loop replacing formula

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.

enter image description here

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