VBA - match function not working on numbers stored as text "1000" and above

JCCinOhio

New Member
Joined
Dec 31, 2016
Messages
4
In column A I have numbers stored as text from "0000" to "2000", and in column B I have numbers stored as text that I want to find the row# for in col A. For example, "0932" and "1040".

Using this:
a = Application.WorksheetFunction.Match(k, Range("a1:a2000"), 0)
where k is a value from col b, a would be the row that the value from col b, appears in col a.

I get a match for anything from "0000" to "0999", but when I go to "1000" or above I get the "Unable to get Match property of Worksheetfunction class" error.

Here's the whole code for the problem I'm talking about.

Sub test()
Dim k As String
For i = 1 To 5
k = Cells(i, 2).Text
a = Application.WorksheetFunction.Match(k, Range("a1:a2000"), 0)
Cells(i + 1, 5) = a
Next i
End Sub

ideas? Please keep it as basic as possible as I'm just a novice.
Thanks!
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Got it.. I guess the way I populated column a it wasn't exactly text... I repopulated the "0000" to "2000" AFTER making the column text (vs general) and it worked.. Before if I did an =IF(a1000="1000",1,0) it would come back false, but now it's coming back true and all is good.
 
Upvote 0
I just tried to replicate your problem and I can't. This worked perfectly for me no matter what lookup value I used:

Code:
Sub findMatch()


Dim k As String, i As Long, a As Long


With Sheet2
    For i = 1 To 5
        k = .Cells(i, 2)
        a = Application.Match(k, Range("A1:A19"), 0)
        Debug.Print "Found " & k & " in row " & a
    Next i
End With


End Sub

Edit: Saw your edit, ok great! :)
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,544
Messages
6,114,239
Members
448,555
Latest member
RobertJones1986

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