WorksheetFunction.Match()

Y

yenn

New Member
Joined
Jul 17, 2003
Messages
5
I've been trying to use WorksheetFunction.Match() in my macro to find from a column if the record I am searching for is repeated in the rows after itself using a loop.

sample of my code

For Each rw In rangeGroup

result = Application.WorksheetFunction.Match(rw, Range(rw.Row + 1, rw.End(xlDown)), 0)

Next r2

But i keep getting this error: "Unable to get the Match property of the WorksheetFunction class"

But if i change the range to start from the same row as my search criteria, the program will work.

Is it true that the range must start from the search record in VBScript programming? As I am able to use a Match function in excel itself. Please advise.

Thank you.

yenn
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
If the MATCH function cannot find a match it returns the #N/A error value on a worksheet. But in VBA it results in a 1004 error. So you need to bypass it:

Code: 
On Error Resume Next
result = Application.WorksheetFunction.Match(rw, Range(rw.Row + 1, rw.End(xlDown)), 0)
On Error Goto 0

You can test for the error, like this:

Code: 
On Error Resume Next
result = Application.WorksheetFunction.Match(rw, Range(rw.Row + 1, rw.End(xlDown)), 0)
If Err = 0 Then
'   No error - do your thing with result
Else
'   Error - act accordingly
End If
On Error Goto 0
 
Upvote 0
Andrew

Perfect answer - correct, clear, concise.
Shame about the other nnnnn postings on the same subject as brought up by Google.
Should have come here first!

Hope you'll forgive me for replying to a posting this old. But this answer deserves to get the Google hit before the others.

Of course Microsoft's error message is pretty misleading - but that is another subject.

Mike
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Textbox on userform not updating when navigating through records
2
Replies
10
Views
476
ipbr21054
I
R
  • Question Question
VBA Code to Find then Highlight Cell that is Found, then Revert to No Fill when pressing Tab (Could be any key)
Replies
1
Views
233
Micron
M
C
  • Question Question
This is an Excel to Access porting issue - Is it possible to convert an Excel VBA FIND method to work correctly in and with Access's VBA ?
4 5 6
Replies
50
Views
2K
Trebor76
T
P
  • Solved
VBA Find first empty cell in row
2
Replies
10
Views
5K
MARK858
MARK858
P
  • Solved
VBA to keep a running log of changes to specific cell
2 3
Replies
20
Views
2K
Joe4
Joe4

Forum statistics

Threads
1,223,937
Messages
6,175,518
Members
452,650
Latest member
Tinfish

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top