Mike,

I'm sorry - I should have told you that you need to dimension your variable
first:

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

That makes it into a number, no doubt.

BUT, since you are already using VALUE in cell B2, then you don't need to do
that: simply

Dim loanorig As Long
loanorig = Range("B2").Value

Don't worry, we'll get you to a working solution....

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