CODEWITHSUNDEEP
vbaexcel
Part_Time_Nerd
Part_Time_Nerd

Reputation: 1014

How to add a dynamic formula to a cell reference using VBA?

I am trying to add a dynamic formula to a cell using VBA. I have seen a couple of post on it but I cannot figure out what I am doing wrong. I am getting a run-time error '1004' for "Method 'Range' of object'_Worksheet' failed". What am I doing wrong?

Sub AddFormulas()

Dim LastNumberRow As Integer

Set countBase = Sheet7.Range("CU2")
colCount = Sheet7.Range(countBase, countBase.End(xlToRight)).Columns.Count

Dim startCount As Integer
startCount = 98

    For i = 1 To colCount

        If IsNumeric(Cells(2, startCount + i)) Then
            Sheet7.Range(3, i).Formula = "=Sheet6!" & Cells(3, startCount + i).Address & "*" & "Sheet7!" & Cells(3, colCount + startCount).Address          
        Else
            'Do some stuff
        End If
    Next i
End Sub

I have added the proposed changes from the comments below but not I get a file explorer popup window. I have altered my code with the following changes:

If IsNumeric(Sheet7.Cells(2, startCount + i)) Then
            Set bSum = Sheet7.Cells(3, colCount + startCount)
            Set bSpr = Sheet6.Cells(3, startCount + i)

            Sheet7.Cells(3, i).Formula = "=Sheet6!" & bSpr.Address() & "*" & "Sheet7!" & bSpr.Address()

Upvotes: 1

Views: 96

Answers (2)

Part_Time_Nerd
Part_Time_Nerd

Reputation: 1014

I got it to work using this solution:

Sub Formulas()
'Adds the formulas to the worksheet

Dim rLastCell As Range

Set countBase = Sheet6.Range("CU2")

'Starts at cell CU2 and counts the number of columns to the right that are being used
colCount = Sheet6.Range(countBase, countBase.End(xlToRight)).Columns.Count


Dim startCount As Integer
startCount = 98 'This is column CT

    For i = 1 To colCount

        'Checks to see if the value in row 2 starting at CU is a number, if it is it adds the index-match formula
        If IsNumeric(Sheet7.Cells(2, startCount + i)) Then
            bSpr = Sheet6.Cells(3, startCount + i).Address(True, False)

            Sheet6.Cells(3, startCount + i).Formula = "=INDEX(ORIG_MAT_DATA_ARRAY,MATCH($W3,MAT_RLOE_COLUMN),MATCH(" _
            & bSpr & ",MAT_CY_HEAD))" & "/" & "INDEX(ORIG_MAT_DATA_ARRAY,MATCH($W3,MAT_RLOE_COLUMN),MATCH(""Total"",ORIG_MAT_CY_HEAD,0))"

        End If

        'Checks to see if the value in row 2 starting at CU is the word "Total", if it is it adds the sum formula
        If Sheet6.Cells(2, startCount + i) = "Total" Then
            startCol = Cells(3, startCount + 1).Address(False, False)
            endCol = Cells(3, startCount + (colCount - 1)).Address(False, False)

            Sheet6.Cells(3, colCount + startCount) = "=SUM(" & startCol & ":" & endCol & ")"
        End If
    Next i

End Sub

Upvotes: 0

EEM
EEM

Reputation: 6660

Try replacing Sheet7.Range(3, i).Formula = ... with: Sheet7.Cells(3, i).Formula = ...

Upvotes: 1

Related Questions