Excel macro run-time error 1004

Question

I was wondering what is causing Run-time error 1004 for this macro when I run it:

    Sub brand()
'
' brand Macro
'
' Keyboard Shortcut: Ctrl+Shift+B
'
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Brand"
    Range("M2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(ISNUMBER(SEARCH(""trident"", RC[-12]))), ""Trident"", IF(OR(ISNUMBER(SEARCH(""stride"", RC[-12]))),""Stride"", IF(OR(ISNUMBER(SEARCH(""mints"", RC[-12]))), ""Mints"", IF(OR(ISNUMBER(SEARCH(""gum bubb"", RC[-12]))), ""Gum Bubb"", IF(OR(ISNUMBER(SEARCH(""gum heritage"", RC[-12]))), ""Gum Heritage"", IF(OR(ISNUMBER(SEARCH(""dentyne"", RC[-12]))), ""Dentyne"", IF" & _
        "MBER(SEARCH(""candy struble"", RC[-12]))), ""Candy Struble"", IF(OR(ISNUMBER(SEARCH(""halls struble"", RC[-12]))), ""Halls"", ""Other""))))))))"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("M2:M525998")
    Range("M2:M525998").Select
    Range("O8").Select
End Sub

I had a similar macro which actually did work when I run the macro:

    Sub location()
'
' location Macro
' fill in location
'
' Keyboard Shortcut: Ctrl+Shift+L
'
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Location"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        " =IF(OR(ISNUMBER(SEARCH(""ontario"",G4))),""Ontario"", IF(OR(ISNUMBER(SEARCH(""carlisle"",G4))),""Carlisle"",IF(OR(ISNUMBER(SEARCH(""orchard"",G4))),""Orchard"",""Other"")))                                                                                                     "
    Range("L2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(OR(ISNUMBER(SEARCH(""ontario"",R[2]C[-5]))),""Ontario"",IF(OR(ISNUMBER(SEARCH(""carlisle"",R[2]C[-5]))),""Carlisle"",IF(OR(ISNUMBER(SEARCH(""orchard"",R[2]C[-5]))),""Orchard"",""Other"")))"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("L2:L425682")
    Range("L2:L425682").Select
    Range("N3").Select
End Sub

Both of these macros where created by recording them and for both I did the same process (the brand macro has a bigger formula). But for some reason the brand macro has run-time error, while the location macro does not. I am new to excel so I was wondering if anyone knows how I can fix it. Thank you.

Look at the way you are concatenating the two lines of the formula. If comes out as IFMBER. — user4039065

Unknown Unknown · Accepted Answer · 2016-06-06T05:00:49

When you broke the long string containing the formula into two lines, you lost several characters.

Sub brand()
' brand Macro
' Keyboard Shortcut: Ctrl+Shift+B

    With Worksheets("Sheet3")   'you should know what worksheet you are planning to put a formula on
        .Range("M1") = "Brand"
        'as an xlA1 style formula
        .Range("M2:M525998").Formula = _
            "=IF(OR(ISNUMBER(SEARCH(""trident"", RC[-12]))), ""Trident"", " & _
             "IF(OR(ISNUMBER(SEARCH(""stride"", RC[-12]))), ""Stride"", " & _
             "IF(OR(ISNUMBER(SEARCH(""mints"", RC[-12]))), ""Mints"", " & _
             "IF(OR(ISNUMBER(SEARCH(""gum bubb"", RC[-12]))), ""Gum Bubb"", " & _
             "IF(OR(ISNUMBER(SEARCH(""gum heritage"", RC[-12]))), ""Gum Heritage"", " & _
             "IF(OR(ISNUMBER(SEARCH(""dentyne"", RC[-12]))), ""Dentyne"", " & _
             "IF(OR(ISNUMBER(SEARCH(""candy struble"", RC[-12]))), ""Candy Struble"", " & _
             "IF(OR(ISNUMBER(SEARCH(""halls struble"", RC[-12]))), ""Halls"", ""Other""))))))))"
        'or as an xlR1C1 style formula
        '.Range("M2:M525998").FormulaR1C1 = _
            "=IF(OR(ISNUMBER(SEARCH(""trident"", RC[-12]))), ""Trident"", " & _
             "IF(OR(ISNUMBER(SEARCH(""stride"", RC[-12]))), ""Stride"", " & _
             "IF(OR(ISNUMBER(SEARCH(""mints"", RC[-12]))), ""Mints"", " & _
             "IF(OR(ISNUMBER(SEARCH(""gum bubb"", RC[-12]))), ""Gum Bubb"", " & _
             "IF(OR(ISNUMBER(SEARCH(""gum heritage"", RC[-12]))), ""Gum Heritage"", " & _
             "IF(OR(ISNUMBER(SEARCH(""dentyne"", RC[-12]))), ""Dentyne"", " & _
             "IF(OR(ISNUMBER(SEARCH(""candy struble"", RC[-12]))), ""Candy Struble"", " & _
             "IF(OR(ISNUMBER(SEARCH(""halls struble"", RC[-12]))), ""Halls"", ""Other""))))))))"
        .Range("O8").Select
    End With
End Sub

Formulas can be written into all cells at once; no need to seed the first cell and then use the Range.AutoFill method.

I've broken the formula into many lines; I prefer to see as much of the valid code in my VBE window as possible.

It concerns me that you have hardcoded the end row. There must be one of hte other columns where you can collect the last row with something like,

dim lrw as long
lrw = .cells(rows.count, "A").end(xlup).row

This allows you to specify the number of rows to receive the formula.

.Range("M2:M" & lrw).Formula = _

tbh, I do not fully understand why you have the OR function in that formula.

Excel macro run-time error 1004

1 Answers