Object Variable or With Block Variable Not Set?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Vik Rubenfeld - 06 Mar 2004 22:07 GMT

I'm running an Excel VBA macro, and getting the "Object variable or With
block variable not set" error. There is no With block in this code. Here
is the code:

theSearchString = SearchStringCell.Value

' the following line verifies that the expected contents are in
' theSearchString
MsgBox theSearchString

' the error occurs on this line:
theFindResult = Selection.Find(What:=theSearchString,
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,_
MatchCase:=False).Activate

How do I fix this? Thanks in advance to all for any info.

-Vik

Reply to this Message

Evan Tomlinson - 06 Mar 2004 22:18 GMT

Well,

The message you get means that you try to work with an object variable that
has no contents / context yet.

In your example I would guess that you probably has got no selection in your
sheet or that the ActiveCell is not existing. To me these two seems to be
conflicting, either you have ONE acitve cell or you have a selection. Or am
I completely wrong here?

Nevertheless, check all variables for their contents in your function call
to see which one is not set yet.

I'm running an Excel VBA macro, and getting the "Object variable or With
block variable not set" error. There is no With block in this code. Here
is the code:

theSearchString = SearchStringCell.Value

' the following line verifies that the expected contents are in
' theSearchString
MsgBox theSearchString

' the error occurs on this line:
theFindResult = Selection.Find(What:=theSearchString,
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,_
MatchCase:=False).Activate

How do I fix this? Thanks in advance to all for any info.

-Vik

Reply to this Message

Vik Rubenfeld - 07 Mar 2004 00:55 GMT

> In your example I would guess that you probably has got no selection in your
> sheet or that the ActiveCell is not existing. To me these two seems to be
> conflicting, either you have ONE acitve cell or you have a selection. Or am
> I completely wrong here?

The following line sets the selection based on the current active cell:

ActiveCell.Offset(-1, 0).Range("A1:A200").Select

When I step through the macro, I can see the selection and the active
cell in the spreadsheet. And if I do the find manually, using the Excel
menus, it works. How can I fix the error message?

-Vik

Reply to this Message

Vik Rubenfeld - 07 Mar 2004 01:45 GMT

Update: It's got something to do with the variable called
theSearchString:

theSearchString = SearchStringCell.Value

'verifies contents of the variable:
MsgBox theSearchString

theFindResult = Selection.Find(What:=theSearchString,
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,_
MatchCase:=False).Activate

Because it works when I try it likes this:

theFindResult = Selection.Find(What:="abcde",
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,_
MatchCase:=False).Activate

The contents of the variable theSearchString have been verified via the
MsgBox line. Is my syntax correct in the use of the variable
theSearchString, in my call to Selection.Find?

Reply to this Message

Steve Gerrard - 07 Mar 2004 04:46 GMT

> Update: It's got something to do with the variable called
> theSearchString:
[quoted text clipped - 19 lines]
> MsgBox line. Is my syntax correct in the use of the variable
> theSearchString, in my call to Selection.Find?

There is a fair chance that the object that is "nothing" is the implied
object returned by the Find method, whose Activate method you are
calling, and whose default property Value you are assigning to
theFindResult. You could make your code more "explicit" by first
retrieving the object, then checking it before assigning its value and
calling its Activate method.

dim foundRange as Range

set foundRange = Selection.Find(What:=theSearchString, _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext,_
MatchCase:=False)

if foundRange is not Nothing then
theFindResult.Value = foundRange.Value
foundRange.Activate
endif

I'm not sure what variable type theSearchString and theFindResult are,
but Find returns a range object. Your code works in some cases because
the default property of a range is its Value.

Reply to this Message

Vik Rubenfeld - 07 Mar 2004 07:07 GMT

Thanks Steve. That was very helpful. Revised as follows, the code is now
working:

Set RangeToSearch = Selection

Set FoundCell = RangeToSearch.Find(theSearchString)

If Not FoundCell Is Nothing Then
[....]
End If

-Vik

Reply to this Message

VB Forum / General 2 / March 2004

Object Variable or With Block Variable Not Set?