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

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

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

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 wrote:

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

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

Thanks Dave. Worked a treat ;o)

"Dave Peterson" wrote in message
...
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 wrote:

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