Error when selecting date range.

[elky.man]

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]

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?

 

[elky.man]

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?















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Dave,

Thank you very much for your quick response. To answer your
question work sheet is in workbook. I tried out your code and it
worked everytime I used it.

-Chris

 

[Dave Peterson]

My real question is about the Range("Date") cell. Is it in the worksheet named
"Work Sheet"?

But since it worked ok, I guess I guessed right.

 

[elky.man]

My real question is about the Range("Date") cell.  Is it in the worksheet named
"Work Sheet"?

But since it worked ok, I guess I guessed right.



(e-mail address removed) wrote:

Dave,
Yes you did guess correctly. The range "Date" is in the active
workbook "WORKBOOK.XLS" on Sheet "Work Sheet".

Thanks,
Chris