CODEWITHSUNDEEP
vbaexcelexcel-formularuntime-error
Sam Mcdonald
Sam Mcdonald

Reputation: 25

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

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.

Upvotes: 0

Views: 49

Answers (3)

Shai Rado
Shai Rado

Reputation: 33692

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 & ")"

Upvotes: 0

user3598756
user3598756

Reputation: 29421

change 

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

to

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

Upvotes: 0

CallumDA
CallumDA

Reputation: 12113

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 *

Upvotes: 1

Related Questions