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

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

On Jul 15, 10:18*am, Dave Peterson wrote:
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- 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

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.

wrote:

<<snipped
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

On Jul 15, 4:13*pm, Dave Peterson wrote:
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.



wrote:

<<snipped
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

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

Thanks,
Chris