"value" works in sheet, not vba
 

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

"value" works in sheet, not vba
 

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

"value" works in sheet, not vba
 

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

"value" works in sheet, not vba
 

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


.

"value" works in sheet, not vba
 

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


.

"value" works in sheet, not vba
 

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

"value" works in sheet, not vba
 

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

"value" works in sheet, not vba
 

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

"value" works in sheet, not vba
 

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

"value" works in sheet, not vba
 

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

"value" works in sheet, not vba
 

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


.

"value" works in sheet, not vba
 

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

"value" works in sheet, not vba
 

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

"value" works in sheet, not vba
 

tom, bernie, brian. thanks so much for your help. tom, i got your other
replies just after i got this one, thx. this seems to cover it all. the
most interesting thing about this code is i can omit the Dim's and type the
range directly into match formula and it still works, but if i use
application.isnumber vs. isnumeric, (which is what i would have done had i
thought about this process, along w/ the above ill-advised shortcuts), it
doesn't work. apparent numbers that are results of formulas such as
=RIGHT()... show FALSE when spreadsheet formula =isnumber(B2) is written,
but are true in code w/: if isnumeric... why are application.isnumber (or
=isnumber()) and isnumeric so different? thanks again, mike allen
"Tom Ogilvy" wrote in message
...
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

"value" works in sheet, not vba
 

Isnumber checks if the value is stored as a number. Isnumeric checks if it
can be treated like a number (in your parlance, does it look like a number).
The second is provided so you can check before you try to convert something
to a number that can not be converted (as one example).

--
Regards,
Tom Ogilvy

"mike allen" wrote in message
...
tom, bernie, brian. thanks so much for your help. tom, i got your other
replies just after i got this one, thx. this seems to cover it all. the
most interesting thing about this code is i can omit the Dim's and type
the
range directly into match formula and it still works, but if i use
application.isnumber vs. isnumeric, (which is what i would have done had i
thought about this process, along w/ the above ill-advised shortcuts), it
doesn't work. apparent numbers that are results of formulas such as
=RIGHT()... show FALSE when spreadsheet formula =isnumber(B2) is written,
but are true in code w/: if isnumeric... why are application.isnumber
(or
=isnumber()) and isnumeric so different? thanks again, mike allen
"Tom Ogilvy" wrote in message
...
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

"value" works in sheet, not vba
 

I am having trouble referencing a cell (or range) .value property from
cell that is returning #N/A (Error 2042) as a result of a faile
formula. The following code fails (sometimes):

If Range("C13").value = "BOCES" THEN
.....
END IF

Cell C13 has a VLookup formula that sometimes returns #N/A instead of
valid lookup value. Oddly enough when the lookup is successful the
the content of Cell C13 is "text" and the code works. However, whe
the formula is unsuccessful then it returns #N/A (Error 2042) which i
an "error object" instead of "text". Consequently my code raises a
"error 13 - type mismatch". I'm suprised to find a function that ca
change it's return type on the fly.

Anyway here is how I was able to make it work.

Dim MyValue as variant
MyValue = Range("C13").value
if MyValue = "BOCES" THEN
....
END IF

Does anyone have a better way to get around this problem???

--
Message posted from http://www.ExcelForum.com

"value" works in sheet, not vba
 

Either check for the error:

if iserror(range("c13").value) then
'do what an error would do
else
if range("c13").value = "BOCES" then
'do ...
end if
end if


or use the .text property:

if range("c13").text = "BOCES" then



"EnigmaCDE <" wrote:

I am having trouble referencing a cell (or range) .value property from a
cell that is returning #N/A (Error 2042) as a result of a failed
formula. The following code fails (sometimes):

If Range("C13").value = "BOCES" THEN
....
END IF

Cell C13 has a VLookup formula that sometimes returns #N/A instead of a
valid lookup value. Oddly enough when the lookup is successful then
the content of Cell C13 is "text" and the code works. However, when
the formula is unsuccessful then it returns #N/A (Error 2042) which is
an "error object" instead of "text". Consequently my code raises an
"error 13 - type mismatch". I'm suprised to find a function that can
change it's return type on the fly.

Anyway here is how I was able to make it work.

Dim MyValue as variant
MyValue = Range("C13").value
if MyValue = "BOCES" THEN
...
END IF

Does anyone have a better way to get around this problem????

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson