VBA type mismatch error

[Dave Peterson]

VBA has its own version of =find().

Take a look at VBAs help for InStr.

=find() will return an error if there isn't a match.

Dim j as Variant 'could be an error
j = application.Find(v, Cells(cnt, 1).Value)
if iserror(j) then
msgbox "not there"
else
'it's there
end if

may work for you, but InStr will be much easier.

 

[Pierre Fichaud]

Hello all,
The following line is in an Excel function:

j = Application.Find(v, Cells(cnt, 1).Value)

When watched, v has a value of "s" with type variant/string.
cells(cnt,1).value has a value of 2 with a type of variant/double.

I assume that the type mismatch occurs because the character "S" can't
be found in an integer. How do I get this to work?

TIA. Pierre.

 

[Pierre Fichaud]

Dave,
Terrific, it worked with InStr. Many thanks. I have another question.
How does one force a cell to be text. Let's say the cell is
cells(cnt,1). What is the appropriate VBA code? TIA. Pierre.

 

[Pierre Fichaud]

Dave,
Also, is there a standard reference work for VBA, one that is
considered the "Bible"? TIA.

 

[Dave Peterson]

You have a few choices.

You could give the cell a numberformat of text:

with activesheet.cells(cnt,1)
.numberformat = "@"
.value = "00001234"
end with

Or you could precede your entry with an apostrophe:

with activesheet.cells(cnt,1)
.value = "'00001234"
'or
.value = "'" & format(1234,"00000000")
end with

Samething goes if you're doing data entry manually--preformat the range as text
or start with an apostrophe.

 

[Dave Peterson]

There are lots of good books.

Debra Dalgleish has a list of books at her site:
http://www.contextures.com/xlbooks.html

John Walkenbach's books are very good to start.