Dim loanorig as Variant
Dim loanorigrow as Variant
Dim rng as Range
set rng = Sheets("loan").Range("a1:a3745")
loanorig = Sheets("main").Range("b2").Value
if isnumeric(loanorig) then
loanorigrow = Application.Match(cdbl(loanorig), _
rng, 0)
else
loanorigrow = Application.Match(loanorig, rng,0)
End if

if iserror(loanorigrow) then
msgbox "No match made"
else
msgbox "Results are row " & rng(loanorigrow).Row
End if


--
Regards,
Tom Ogilvy

"mike allen" wrote in message
...
as Double works. i had tried dim as text, as long... thank you. i do
have one more real dilemma with this process, though. what if A1 has a
"#"
at the end of it? like: 548832# some of the cells will have nothing
but
numbers, some will have letters and/or symbols in them. if they have
letters/symbols (truly text format), my old way works. if they have only
numbers in the string, your way works (dim as double). i need something
that will work on both. any thoughts? thanks, mike allen

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Mike,

In addition, you might want to dimension loanorig as a Double rather
than
a
Long....

HTH,
Bernie
MS Excel MVP

"mike allen" wrote in message
...
Bernie, thanks for the help, but unfortunately, that did not get it.
i
get
'error 2042' using Cstr and 'overflow' using CLng.

the problem is B2 is a formula that takes all digits of another cell
except
the first digit: =right(A2,len(A2)-1) this makes B2 text, while
A2
is
a
number. I can find a match for A2 easily, but not B2, even though
they
are
both clearly in the array of thousands. the only thing that works is
if
i
wrap Value around the formula in B2 on the spreadsheet, but there are
reasons i will not be able to do that every time. I need to be able
to
do
the same thing in code. thanks, mike allen

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Mike,

On re-reading your post, I realized that you may need to use
something
like:

loanorig = CStr(Sheets("main").Range("B2").Value)

or, more probably,

loanorig = CLng(Sheets("main").Range("B2").Value)

It depends on how your data is acutally entered.

HTH,
Bernie
MS Excel MVP

"mike allen" wrote in message
...
why would a formula on a spreadsheet work, while reproducing it in
code
doesn't? i must be writing code incorrectly.

spreadsheet formula:
=MATCH(VALUE(B2),loan!$A$1:$A$3745,0)

code:
loanorig = Sheets("main").Range("b2")
loanorigrow = Application.Match(loanorig.Value,
Sheets("loan").Range("a1:a3745"), 0)

any thoughts? i am really having problems w/ comparing and
transforming
text to values and vice versa. ALL I AM TRYING TO DO IS MATCH
THINGS
THAT
LOOK ALIKE, but am failing miserably b/c one may be a number, the
other
text
(text only b/c it is a formula result---from a number of all
things).
reformatting cells does no good. if i can at least get the above
problem
worked out, i can get by for now. thanks, (disdraught w/ excel)
mike
allen