VBA's Find and dates can be a pain to work with.

Since your data is in a single row (or column), you could use
application.match(). And Application.match() plays much nicer with dates.

Option Explicit
Sub COPY_PASTE()
Dim RngToCopy As Range
Dim RngToSearch As Range
Dim res As Variant

With ActiveSheet
Set RngToCopy = .Range("AI7:AI299")
End With

With Workbooks("WORKBOOK.XLS").Worksheets("work sheet")
Set RngToSearch = .Range("b3", .Range("b3").End(xlToRight))

res = Application.Match(CLng(.Range("date").Value), RngToSearch, 0)
End With

If IsError(res) Then
MsgBox "No match!"
Else
RngToCopy.Copy
RngToSearch(res).Offset(4, 0).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End If
End Sub

Range("Date") is on "work sheet" in "workbook.xls" right?




wrote:

I have a little piece of code that worked in one workbook, however;
when I copied it over to another workbook I get the following error:
Run-Time error '1004':
Method 'Range' of object '_Global' failed

The code used copies to a column based on the date with the date range
starting in B3 and going until AF3. It selected the range correctly,
but just fails. What I don't understand is why the exact same code
will work in one workbook and not in another.

Of note the Macro's are held in a seperate workbook that opens a total
of 3 other workbooks.
The workbook and worksheet that uses the code are the active workbook/
worksheet.

Here is the code used:

Sub COPY_PASTE()
'
Range("AI7:AI299").Select
Selection.Copy
Application.Run ("DateColumn")
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
End Sub

Sub DateColumn()
Windows("WORKBOOK.XLS").Activate
Sheets("WORK SHEET").Select
Range("B3").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Find(What:=Range("DATE"), After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(4, 0).Select

End Sub

Any help will be greatly appreciated.

Thanks,
Christopher

--

Dave Peterson