error in vba code

Posted on 2011-05-04
Last Modified: 2012-05-11

Could someone please tell me what is the problem with my vba code? When I try to debug it I keep getting that 'error 1004 application-defined or object-defined error'. I have tried different things with the syntax but I can't seem to find the issue. Thanks.

Sub Multiplier()

' Updates Multiplier column with '1' or '-1'
  Dim lastRow As Long
    lastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("E2").Formula = "=IF(OR(A2=41826,A2=50529,A2=50530,A2=51807,A2=51828,A2=51850,A2=51851,A2=51852,A2=51853,A2=51854,A2=51855,A2=51856,A2=51857,A2=51858,A2=51859,A2=51860,A2=51861,A2=51862,A2=51863,A2=51864,A2=51865,A2=51866,A2=51899,A2=E5184_I,A2=E5732_I) ""-1"", ""1"")"
    Range("E2").AutoFill Destination:=Range("E2:E" & lastRow), Type:=xlFillDefault

End Sub
Question by:erp1022
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    I have highlighted the error producing parts in bold

        Range("E2").Formula = "=IF(OR(A2=41826,A2=50529,A2=50530,A2=51807,A2=51828,A2=51850,A2=51851,A2=51852,A2=51853,A2=51854,A2=51855,A2=51856,A2=51857,A2=51858,A2=51859,A2=51860,A2=51861,A2=51862,A2=51863,A2=51864,A2=51865,A2=51866,A2=51899,A2=E5184_I,A2=E5732_I),""-1"", ""1"")"
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    there are two "_I" s and one comma after the second _I
    LVL 43

    Expert Comment

    by:Saqib Husain, Syed
    Second try with underlines
        Range("E2").Formula = "=IF(OR(A2=41826,A2=50529,A2=50530,A2=51807,A2=51828,A2=51850,A2=51851,A2=51852,A2=51853,A2=51854,A2=51855,A2=51856,A2=51857,A2=51858,A2=51859,A2=51860,A2=51861,A2=51862,A2=51863,A2=51864,A2=51865,A2=51866,A2=51899,A2=E5184_I,A2=E5732_I),""-1"", ""1"")"
    LVL 43

    Assisted Solution

    by:Saqib Husain, Syed
    Still not much improvement.

    Third try using picture
    LVL 51

    Expert Comment


    LVL 51

    Accepted Solution

    I mean this code

      Range("E2").Formula = "=IF(OR(A2=41826,A2=50529,A2=50530,A2=51807,A2=51828,A2=51850,A2=51851,A2=51852,A2=51853,A2=51854,A2=51855,A2=51856,A2=51857,A2=51858,A2=51859,A2=51860,A2=51861,A2=51862,A2=51863,A2=51864,A2=51865,A2=51866,A2=51899,A2=""E5184_I"",A2=""E5732_I""), ""-1"", ""1"")"

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Suggested Solutions

    Title # Comments Views Activity
    Automate double vlookup using VBA 11 57
    Excel File Date Format 6 16
    Using "ScreenUpdating" 6 36
    Posting V12 2 16
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now