"value" works in sheet, not vba

M

mike allen

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
 
B

Bernie Deitrick

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)
Click to expand...

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
 
B

Bernie Deitrick

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
 
B

Brian

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

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
 
M

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 said:
Mike,
Click to expand...
 
B

Bernie Deitrick

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 said:
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
Click to expand...
 
B

Bernie Deitrick

Mike,

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

HTH,
Bernie
MS Excel MVP

mike allen said:
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
Click to expand...
 
T

Tom Ogilvy

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.
 
M

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 said:
Mike,

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

HTH,
Bernie
MS Excel MVP

mike allen said:
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
Click to expand...
is
Click to expand...
 
T

Tom Ogilvy

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.
 
T

Tom Ogilvy

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
 
T

Tom Ogilvy

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, _
 
M

mike allen

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
 
T

Tom Ogilvy

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).
 
E

EnigmaCDE

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???
 
D

Dave Peterson

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
 
Top