Run Time Error '1004', have researched and tried several recommended solutions to no avail

Question

I am trying to fill a range with a formula and continue to get a runtime error '1004'. The error occurs at the line I have starred Sheets("Forecast").Range("H125").Formula = formulaTest. The code in my Sub is as follows:

Sub FirmShareFill()

Dim RampUp As Range
Dim RampBas As Range
Dim RampDn As Range
Dim Numbering As Range
Dim Approval As Range
Dim PeakShare As Range



Dim tcount As Byte
Dim bcount As Byte
Dim ubdcount As Byte
Dim yearRange2 As Byte

year = Worksheets("Inputs").Range("B6").Value
cntry = Worksheets("Inputs").Range("B5").Value
bnd = Worksheets("Inputs").Range("B3").Value
typ = Worksheets("Inputs").Range("B2").Value
cat = Worksheets("Inputs").Range("B4").Value

tcount = bnd * cat + bnd
ubdcount = tcount * 2 + 1
yearCount = year * 4 - 1

        For ubd = 1 To 3
        For t = 1 To typ
        For b = 1 To bnd
        For c = 1 To cat
        For i = 1 To cntry

        Set RampUp = Columns(7).Find(What:="Ramp_Up" & i, MatchCase:=True).Offset(0, 1)
        Set RampBas = Columns(7).Find(What:="Ramp_Bas" & i, MatchCase:=True).Offset(0, 1)
        Set RampDn = Columns(7).Find(What:="Ramp_Dn" & i, MatchCase:=True).Offset(0, 1)

        Set Numbering = Sheets("Inputs").Range("B13")
        Set Approval = Columns(6).Find(What:="Approval", MatchCase:=True).Offset(i, 2 + ubd)


        bcount = c + (cat + 1) * (b - 1)

        If t = 1 And b = 1 And ubd = 1 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share", MatchCase:=True).Offset(4 + i, c)
        ElseIf t = 1 And b > 1 And ubd = 1 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share" & c, MatchCase:=True).Offset(4 + i, bcount)
        ElseIf t > 1 And b = 1 And ubd = 1 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share" & c, MatchCase:=True).Offset(4 + i, c + tcount)
        ElseIf t > 1 And b > 1 And ubd = 1 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share" & c, MatchCase:=True).Offset(4 + i, tcount + bcount)

        ElseIf t = 1 And b = 1 And ubd = 2 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share", MatchCase:=True).Offset(4 + i, c + ubdcount)
        ElseIf t = 1 And b > 1 And ubd = 1 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share" & c, MatchCase:=True).Offset(4 + i, bcount + ubdcount)
        ElseIf t > 1 And b = 1 And ubd = 1 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share" & c, MatchCase:=True).Offset(4 + i, c + tcount + ubdcount)
        ElseIf t > 1 And b > 1 And ubd = 1 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share" & c, MatchCase:=True).Offset(4 + i, tcount + bcount + ubdcount)

        ElseIf t = 1 And b = 1 And ubd = 3 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share", MatchCase:=True).Offset(4 + i, c + 2 * ubdcount)
        ElseIf t = 1 And b > 1 And ubd = 3 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share" & c, MatchCase:=True).Offset(4 + i, bcount + 2 * ubdcount)
        ElseIf t > 1 And b = 1 And ubd = 3 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share" & c, MatchCase:=True).Offset(4 + i, c + tcount + 2 * ubdcount)
        ElseIf t > 1 And b > 1 And ubd = 3 Then
            Set PeakShare = Columns(5).Find(What:="Peak Share" & c, MatchCase:=True).Offset(4 + i, tcount + bcount + 2 * ubdcount)
        End If

        Dim formulaTest As String

        formulaTest = "=IF(" & Numbering.Address(False, False) & "<" & Approval.Address & ","", " & PeakShare.Address & " * " & RampUp.Address & ")"

        If ubd = 1 Then
        **Sheets("Forecast").Range("H125").Formula = formulaTest**
        ActiveCell.Offset(1, 0).Select

        ElseIf ubd = 2 Then
        Range(ActiveCell, ActiveCell.Offset(0, yearRange2)).Formula = "=IF(" & Numbering.Address(False, False) & " < " & Approval.Offset(1, 0).Address & ","", " & PeakShare.Address & " * " & RampBas.Address & ""

        ElseIf ubd = 3 Then
        Range(ActiveCell, ActiveCell.Offset(0, yearRange2)).Formula = "=IF(" & Numbering.Address(False, False) & " < " & Approval.Offset(1, 0).Address & ","", " & PeakShare.Address & " * " & RampDn.Address & ""

        End If


        Next i

        ActiveCell.Offset(1, 0).Select

        Next c
        Next b
        Next t
        Next ubd



End Sub

I believe the error may have something to do with how I declared the range "numbering" range, but as of yet I have been unable to figure it out. I have used this code on the same sheet many times, the only difference being that I have set a range, numbering, on a different sheet.

You need to escape the quotes in your formula: `... Approval.Address & ","""", " & PeakShare.Address ...` — Comintern, Mar 01 '17 at 16:21
@Mat'sMug I've installed Rubberduck, but i do not see it in my com add ins or vba edtior menu, help? — pokemon_Man, Mar 01 '17 at 16:55
@Mat'sMug done, please assist http://stackoverflow.com/questions/42538026/can-not-see-the-add-in-after-installing-rubberduck — pokemon_Man, Mar 01 '17 at 17:20

CallumDA · Accepted Answer · 2017-03-01T16:23:50.073

1

This should work:

formulaTest = "=IF(" & Numbering.Address(False, False) & "<" & Approval.Address & ",""""," & PeakShare.Address & "*" & RampUp.Address & ")"

As @Comintern pointed out, you need to use """" to include double empty speech marks in your formula. I also removed the spaces either side of the *

edited Mar 01 '17 at 16:23

answered Mar 01 '17 at 16:22

CallumDA

12,025
6
30
52

And why would it? – Mathieu Guindon Mar 01 '17 at 16:22

score 0 · Answer 2 · answered Mar 01 '17 at 16:25

0

change

IF(" & Numbering.Address(False, False) & "<" & Approval.Address & ","", " & PeakShare.Address & " * " & RampUp.Address & ")"

to

IF(" & Numbering.Address(False, False) & "<" & Approval.Address & ","""", " & PeakShare.Address & " * " & RampUp.Address & ")"

answered Mar 01 '17 at 16:25

user3598756

28,893
4
18
28

1

SamMcdonald; but @CallumDA 's answer was the same as mine and faster: you should accept his! – user3598756 Mar 01 '17 at 16:42

score 0 · Answer 3 · answered Mar 01 '17 at 16:54

Instead of counting how many " you have, you can use Chr(34) inside " to have a Formula check for ".

In your case, use:

"=IF(" & Numbering.Address(False, False) & "<" & Approval.Address & "," & Chr(34) & ", " & PeakShare.Address & " * " & RampUp.Address & ")"

Run Time Error '1004', have researched and tried several recommended solutions to no avail

3 Answers3