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

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

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.

I think your problem is in the line
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

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

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

Oops, Mike, you're right.

Sam