Results 1 to 6 of 6

Thread: Error 1004

  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Error 1004

    I have taken this code from something else I have done and amended it to suit my new need (just the Column header and Formula)

    It keeps telling me when the code is run that I have a 1004 error
    Method 'Range' of object '_Global' Failed

    I cant understand why this message appears

    Sub BuildData()
    Dim DataMaxRow As Long
    DataMaxRow = ThisWorkbook.Sheets("Lookup").Range("DataMaxRow"). Value + 2
    With ThisWorkbook.Sheets("AgedAccounts")
    .Range(("B6"), ThisWorkbook.Sheets("AgedAccounts").Range("B" & CStr(DataMaxRow))).Formula = "=VLOOKUP(C6,Contracts,2,0)"
    .Range(("G6"), ThisWorkbook.Sheets("AgedAccounts").Range("G" & CStr(DataMaxRow))).Formula = "=MONTH(F6)"

    Range(("A5"), ThisWorkbook.Sheets("AgedAccounts").Range("H" & CStr(DataMaxRow))).Copy
    .Range("A5").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    End With
    End Sub

  2. #2
    Join Date
    Dec 2004
    Posts
    37

    Try a full stop

    Hi,

    Just a guess here and someone may have a different answer but Error 1004 is a general error so I usually look for something simple first.

    Try putting a full stop before the 3rd Range statement.

    I would also ensure that all the named range "DataMaxRow" exists in the worksheet.

    Finally I have found that sometimes paste commands can be specific to the version of Excel.

    If all else fails try putting a breakpoint in the code at
    DataMaxRow = ThisWorkbook.Sheets("Lookup").Range("DataMaxRow"). Value + 2
    and then stepping through the code to see which line causes the error.

    Hope this helps
    Mike

  3. #3
    Join Date
    Oct 2003
    Posts
    1,091
    In addition, check this:

    Why are you using this: CStr(DataMaxRow)?
    I would think you should use DataMaxRow, which is a number, which is what is expected.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I think your problem is in the line
    Code:
    .Range(("B6"), ThisWorkbook.Sheets("AgedAccounts").Range("B" & CStr(DataMaxRow))).Formula = "=VLOOKUP(C6,Contracts,2,0)"
    Since Contracts is obviously a named range, it needs to be in quotes within the VLOOKUP function. However, since it's already inside of a quote, I'm not sure how to fix it.

    Sam

  5. #5
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Elaborating on Mike's and Sam's posts, make sure all the Sheet names and range names used exist, that is the most likely course, and you don't need quotes in the VLOOKUP formula Contracts is a range name

    Code:
    Sub BuildData()
    Dim DataMaxRow As Long
    DataMaxRow = ThisWorkbook.Sheets("Lookup").Range("DataMaxRow").Value + 2
        With ThisWorkbook.Sheets("AgedAccounts")
            .Range("B6", .Range("B" & DataMaxRow)).Formula = "=VLOOKUP(C6,Contracts,2,0)"
            .Range("G6", .Range("G" & DataMaxRow)).Formula = "=MONTH(F6)"
            
            .Range("A5", .Range("H" & DataMaxRow)).Copy
            .Range("A5").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        End With
    End Sub
    As Shades indicated I have removes CStr() as integers concatenate into string without problems in VB and VBA without problems, but I think you would need CStr() in .NET ? and other languages.

    I have also removed ThisWorkbook.Sheets("AgedAccounts") inside the With block otherwise there is no point in using With !


    This code runs but obviously it does not make sense as I do not have you data structure.


    HTH


    MTB
    Last edited by MikeTheBike; 06-28-07 at 05:08.

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    you don't need quotes in the VLOOKUP formula Contracts is a range name
    Oops, Mike, you're right.

    Sam

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •