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

Question

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()

Sheet7.Range(3, i).Formula should be Sheet7.Cells(3, i).Formula — Scott Craner
– Scott Craner, Commented Mar 28, 2017 at 22:49
For that matter make sure that you are referencing the correct work sheet in the IsNumeric(): Sheet7.Cells(2, startCount + i) or Sheet6.Cells(2, startCount + i) — Scott Craner
– Scott Craner, Commented Mar 28, 2017 at 22:51
Also, what sheet do you expect Cells(3, startCount + i) and Cells(3, colCount + startCount) to be on? You should add the worksheet name/index before that too, like for Sheet7.Range(). — BruceWayne
– BruceWayne, Commented Mar 28, 2017 at 22:51
@BruceWayne as the OP is only returning the string address of the cell at those coordinates, it does not really matter on which sheet the cell resides. — Scott Craner
– Scott Craner, Commented Mar 28, 2017 at 22:52

EEM · Accepted Answer · 2017-03-28 23:06:43Z

1

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

answered Mar 28, 2017 at 23:06

EEM

6,6582 gold badges20 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Part_Time_Nerd · Accepted Answer · 2017-04-04 14:04:13Z

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

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related