"Match" function returns wrong value

[Guest]

Hi,
I am trying to simply return the position of a value in an array using the
"Match" function and I seem to be getting an incorrect result.

I am using the following code:

Sub testmatch()
Workbooks("Book1").Activate
MsgBox (Application.WorksheetFunction.Match("Invision",
Workbooks("Book1").Sheets("Sheet1").Range("A:A")))
End Sub

"Invision" is in row 9 of column A. However, the MsgBox displays 284. There
are entries in rows 2 through 363 if that is of any help.

I would be very grateful for any assitance you could provide. I am using
Visual Basic 6.3 and MS Excel 2003 SP2.

Thanks.

Darrell

 

[Dave Peterson]

I would think with text, you'd be looking for an exact match.

I'd use something like:

Sub testmatch()
dim res as variant
res = application.match("Invision", _
Workbooks("Book1.xls").Sheets("Sheet1").Range("A:A"), 0)

if iserror(res) then
msgbox "Not found"
else
msgbox res
end if

End Sub

Notice the extra 0 in the =match() function.

ps. You don't need to activate the workbook for this to work. And it's better
to include the extension (assuming the file has been saved).

 

[Guest]

Hi Dave,
I cannot believe I made such a basic error. I do know that to get an exact
match it is necessary to specify a "0" in the third field of the Match
function as I do this all the time in Excel spreadsheet cells, but I guess
the long code and the fact that I cannot see the entire line in the window,
due to its length, caused me to make this mistake. Thanks again for pointing
out this basic error.

Is there some way of wrapping the code in the window so that I can see the
entire code without having to scroll side to side? Or is it better to leave
it as it is?

Thanks again,

Darrell

 

[Chip Pearson]

Is there some way of wrapping the code in the window so that I can see the

entire code without having to scroll side to side? Or is it better to
leave
it as it is?

There is no autowrap feature. You can, however, split one logical line of
code into several separate physical lines of code using the
<space><underscore> character sequence at the end of a physical line of
code. E..g,

Range _
("A1") _
..Value _
= _
123

is seen by VBA as

Range("A1").Value = 123

Note that there must be a space preceding the underscore.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Guest]

Hi Chip,
Thanks. I may try that.

I have another problem which just occured. I am getting an "Unable to get
Vlookup property of the worksheet function class" error with the following
line of code:

Cells(i, j) = Application.WorksheetFunction.VLookup(temp1, Workbooks("2006
Jan-Sep CAPEX Loadsheet Actuals.xls").Sheets("Worksheet
Totals").Range("F:CO"), 11 + j - 15, False)

Note that i=4, j=15, temp1 is "E-Tools (E-HR)" and there is data in columns
F through CO in rows 1 through 363.

Thanks.

Darrell
------------------

 

[Dave Peterson]

Try dropping the .worksheetfunction from that line.

Just use application.vlookup(...)

Hi Chip,
Thanks. I may try that.

I have another problem which just occured. I am getting an "Unable to get
Vlookup property of the worksheet function class" error with the following
line of code:

Cells(i, j) = Application.WorksheetFunction.VLookup(temp1, Workbooks("2006
Jan-Sep CAPEX Loadsheet Actuals.xls").Sheets("Worksheet
Totals").Range("F:CO"), 11 + j - 15, False)

Note that i=4, j=15, temp1 is "E-Tools (E-HR)" and there is data in columns
F through CO in rows 1 through 363.

Thanks.

Darrell
------------------

 

[Gary Keramidas]

download mz tools, that's one of it's features
http://www.mztools.com/index.htm

--


Gary

Hi Chip,
Thanks. I may try that.

I have another problem which just occured. I am getting an "Unable to get
Vlookup property of the worksheet function class" error with the following
line of code:

Cells(i, j) = Application.WorksheetFunction.VLookup(temp1, Workbooks("2006
Jan-Sep CAPEX Loadsheet Actuals.xls").Sheets("Worksheet
Totals").Range("F:CO"), 11 + j - 15, False)

Note that i=4, j=15, temp1 is "E-Tools (E-HR)" and there is data in columns
F through CO in rows 1 through 363.

Thanks.

Darrell
------------------

 

[Guest]

Your suggestion does work. However, I am confused. When am I supposed to use
application.worksheetfunction.vlookup(...) and when am I supposed to use
application.vlookup(...)? What is the rationale?

Thanks.

Darrell

 

[Dave Peterson]

application.match() and application.worksheetfunction.match()
and
application.vlookup() and application.worksheetfunction.vlookup()

work the same way.

I don't have any idea why it was implemented this way, but you have a couple of
options:

====
saved from a previous post.

Dim res1 as variant 'may return an error (like #n/a in the worksheet
dim res2 as variant
dim myStr as string

res1 = application.vlookup(xcombo.value, searchrange, 3, false)
res2 = application.vlookup(ycombo.value, searchrange, 5, false)

if iserror(res1) _
or iserror(res2) then
mystr = "Some kind of error with vlookup"
elseif isnumeric(res1) _
and isnumeric(res2) then
mystr = format(res1 + res2, "#,###.00") 'or no format???
else
mystr = "at least one non-numeric found"
end if

nonfincalcLabel.Caption = mystr

====
Untested, uncompiled. Watch for typos.

Application.vlookup() returns an error that you can test with iserror().

application.worksheetfunction.vlookup() causes a runtime error that you have to
catch.

dim res as variant
on error resume next
res = application.worksheetfunction.vlookup(....)
if err.number <> 0 then
'an error was found
err.clear
else
'no error!
end if
on error goto 0

application.vlookup() looks/works much easier (well, to me).

 

[Guest]

Hi Dave,
Just wanted to say thank you for all your help.

Darrell