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