Type Mismatch error

[Barb Reinhardt]

I'm getting a Type Mismatch error at the + on this line. I'm not exactly
sure what's supposed to be done here (I'm sure it's searching for something),
so don't know why the + is here.

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

It's dimensioned as follows:

Public myVar() As String

Can someone assist?

Thanks,
Barb Reinhardt

 

[Jim Thomlinson]

What it is looking for is the string
" + myVar + "
which makes absolutely no sense based on the declaration. I assume you did
not write it? As a guess someone was recording a macro and typed in " + myVar
+ " in the find box. You need to create a loop which traveres all of the
items in the array and do the find that way. Based on the code you have
posted in the past you should be able to manage that (or get darned close).
Make sure that you set a range object to the resutls of the find and then
determine if the object is nothing...

If you wnat more help just reply.

 

[Bill Renaud]

You have dimensioned myVar as an array of strings. If so, then your code
should probably be:

Selection.Find(What:=myVar(1), _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If myVar is a single string, then it should have been declared like so:

Dim myVar As String

Assuming the 2nd case, I would rewrite this code like the following:

Dim myVar As String
Dim rngFindResult As Range

Set rngFindResult = Selection.Find(What:=myVar, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

If rngFindResult Is Nothing _
Then
'Error message here?
Else
rngFindResult.Activate
End If

Or, maybe the What parameter should be:

What:="" & myVar & "",

....if you need to have a single double apostrophe appended onto the front
and back of myVar for some reason (I notice that your Find method is
looking in formulas).

In general, if you are concantenating strings together, then use the
ampersand character ("&"), not the plus sign ("+").
If you need to convert a value to a string, then you might have to use
something like:

CStr(myVar)

You might even add another variable and get it set to exactly what the Find
method is searching for (extra double quotes and all). Then you can
single-step through the code and inspect the value of this new variable in
the Locals window.

 

[Barb Reinhardt]

I've subsequently found that the procedure with the error isn't even used.

Thanks for your assistance.

Barb Reinhardt