Run-time error '91': "Object variable or With block variable not set

[Mike]

Run-time error '91': "Object variable or With block variable not set in
the following code" occurs on the following line:

..Find(What:="*/*/04", After:=ActiveCell, LookIn:=xlValues,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate

I don't know why...can someone help??? The code is shown below.

Thanks In Advance,
Mike Taylor

Option Explicit

Sub TestCopyTo2005()
Dim rDest As Range
Dim rSource As Range
Dim Ssh As Worksheet
Dim Dsh As Worksheet

Set Ssh = ThisWorkbook.Sheets("Revenue-Client Data Entry")
Set Dsh = Workbooks("2005.xls").Sheets("Revenue-Client Data Entry")

With Workbooks("2004.xls").Worksheets("Revenue-Client Data
Entry").Range("B4:AJ305")
..Select
..Sort Key1:=Range("B4"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _ Orientation:=xlTopToBottom

'Code errors on the following line:
..Find(What:="*/*/04", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

End With

Set rSource = Range(ActiveCell, Range("AJ305"))
Set rDest = Workbooks("2005.xls").Sheets("Revenue-Client Data _
Entry").Range("B4")

rSource.Copy rDest

End Sub

 

[Bob Phillips]

Mike,

I think the error occurs when not found. Try

Dim rDest As Range
Dim rSource As Range
Dim Ssh As Worksheet
Dim Dsh As Worksheet
Dim oCell As Range

Set Ssh = ThisWorkbook.Sheets("Revenue-Client Data Entry")
Set Dsh = Workbooks("2005.xls").Sheets("Revenue-Client Data Entry")

With Ssh.Range("B4:AJ305") 'Workbooks("2004.xls").Worksheets("Revenue-Client
Data Entry").Range("B4:AJ305")
.Select
.Sort Key1:=Range("B4"), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

'Code errors on the following line:
On Error Resume Next
.Find(What:="/04", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate

End With

Set rSource = Range(ActiveCell, Range("AJ305"))
Set rDest = Workbooks("2005.xls").Sheets("Revenue-Client Data
Entry").Range("B4")

rSource.Copy rDest

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Sharad]

If .Find result is nothing then it will give that error, because of the
last .Activate part. If no match is found it is not referring to any
cell, so .Active for nothing will give that error.

Change the code as under & try:

Dim myRange As Range
Set myRange = .Find(What:="*/*/2004", After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not myRange Is Nothing Then myRange.Activate

Sharad