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

Mike,

The default property of a range object is .Value:

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

So, your code sets loanorig to the Value of Cell B2 on worksheet "main"

Try this:

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

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

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

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

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

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

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

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

Try This:
Set MyRange = Sheets("loan").Range("A1:A3745")
loanorig = Sheets("main").Range("b2")
loanorigrow = Application.WorksheetFunction.Match
(loanorig,MyRange, 0)


-----Original 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


.

Brian, thanks for the help, but unfortunately, that did not get it.
"unable to get the Match property of the WorksheetFunction class" shows up.
It appears as though you have the same thing as me, except in different
order. the range is the same, B2 is the same, and the match function inputs
are the same except for the "worksheetfunction." insertion. if i take out
that insertion, i get just what mine gets: error 2042 on the 'match' line.

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 =value(right(A2,len(A2)-1)) 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 done in code. thanks, mike allen


"Brian" wrote in message
...
Try This:
Set MyRange = Sheets("loan").Range("A1:A3745")
loanorig = Sheets("main").Range("b2")
loanorigrow = Application.WorksheetFunction.Match
(loanorig,MyRange, 0)


-----Original 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


.

When match is not successful, if you use worksheetfunction, it raises the
trappable error you cite. If you don't use worksheet function it returns
#N/A just like in the worksheet (error 2042 is the error value of #N/A -
cvErr(xlErrNA) returns error 2042)

See my other post for a method that should work.

--
Regards,
Tom Ogilvy

"mike allen" wrote in message
...
Brian, thanks for the help, but unfortunately, that did not get it.
"unable to get the Match property of the WorksheetFunction class" shows
up.
It appears as though you have the same thing as me, except in different
order. the range is the same, B2 is the same, and the match function
inputs
are the same except for the "worksheetfunction." insertion. if i take out
that insertion, i get just what mine gets: error 2042 on the 'match' line.

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 =value(right(A2,len(A2)-1)) 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 done in code. thanks, mike allen


"Brian" wrote in message
...
Try This:
Set MyRange = Sheets("loan").Range("A1:A3745")
loanorig = Sheets("main").Range("b2")
loanorigrow = Application.WorksheetFunction.Match
(loanorig,MyRange, 0)


-----Original 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


.

Dim loanorig as Double
Dim loanorigrow as Variant
Dim rng as Range
set rng = Sheets("loan").Range("a1:a3745")
loanorig = Sheets("main").Range("b2").Value
loanorigrow = Application.Match(loanorig.Value, _
rng, 0)

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


you can use loanorigrow directly to show the row, but this is only because
the range starts in row 1. The method shown is more flexible.

--
Regards,
Tom Ogilvy

"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

didn't get you original completely cleaned up

loanorigrow = Application.Match(loanorig.Value, _


should not have the value on the end

loanorigrow = Application.Match(loanorig, _

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
Dim loanorig as Double
Dim loanorigrow as Variant
Dim rng as Range
set rng = Sheets("loan").Range("a1:a3745")
loanorig = Sheets("main").Range("b2").Value
loanorigrow = Application.Match(loanorig.Value, _
rng, 0)

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


you can use loanorigrow directly to show the row, but this is only because
the range starts in row 1. The method shown is more flexible.

--
Regards,
Tom Ogilvy

"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