"object variable or with bock variable not set" - quick reply greatly appreciated!

[Tim]

Hi,

My VBA is having problems (resulting in the above error message) at the
following line: -

Range(Range("RefNum").Find(Ref, LookAt:=xlWhole).Address).Select

- 'RefNum' is a dynamic range (and refers to the correct range when chosen
via 'Edit', 'Goto')
- 'Ref' is a variable which it recognises as '1' when the mouse hovers over
the error-highlighted code

Its purpose is to locate the correct row on another sheet (which contains
the 'RefNum' range) from a unique reference number ('Ref') on this sheet
(then copy relevant pieces of data from one to the other).

This forms part of a much bigger piece of vba, and was previously working
properly; i guess i've changed something, but i can't see what. Can anyone
tell me what i'm doing wrong? Do i need to post the rest of the code?

Thanks,

Tim

 

[Dave Peterson]

If Ref isn't found, then the .address will fail.

..find inherits all the settings that the last Find (either in code or by the
user) applied.

I'd do:

Dim FoundCell as range
...
with activesheet.range("RefNum")
Set foundcell = .Cells.Find(what:=ref, _
after:=.cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
msgbox Ref & " wasn't found"
else
'must be on the activesheet
foundcell.select
end if

 

[Tim]

Thanks Dave. Worked a treat ;o)

If Ref isn't found, then the .address will fail.

.find inherits all the settings that the last Find (either in code or by
the
user) applied.

I'd do:

Dim FoundCell as range
...
with activesheet.range("RefNum")
Set foundcell = .Cells.Find(what:=ref, _
after:=.cells(.cells.count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
msgbox Ref & " wasn't found"
else
'must be on the activesheet
foundcell.select
end if