Unable To Find Match In My Data

[Ark68]

I have run into a problem with my attempt to use the Match function with VBA.

consider this code:

Private Sub UserForm_Initialize()
    Dim qw As String
    Dim tqw As String
    Dim nRID As Double
    Dim nsvc As Long, psvcnum As Long, rwprid As Long
    Dim evtf As String, psvcdiv As String, pvrid as string
    
    Me.Caption = "TOURNAMENT SERVICE:  " & tcnt & " of " & ref
Stop
   
    trid = ridt 'rid (text)
    nRID = CDbl(trid) 'rid (number)
    nsvc = tsvcnum 'service (-#) number
    Debug.Print "Full Toun. RID: " & trid & "-" & nsvc
    
    'previous service division
    psvcnum = nsvc - 1
    If psvcnum = 0 Then
        pvrid = trid
    Else
        pvrid = trid & "-" & psvcnum
    End If
    rwprid = Application.WorksheetFunction.Match(CStr(pvrid), ws_master.Columns(1), 0)  'i used CStr to convert any numbers to string so the comparison is between two strings
    evtf = ws_master.Cells(rwprid, 5)
    psvcdiv = Split(Split(evtf, ")")(0), "(")(1)
    Me.lb_pdiv.Caption = psvcdiv

I am getting an error with the line in red ("Unable to get the Match property of the WorksheetFunction class") I am trying to find a match of string variable 'pvrid' with the mix of string ("########-#") and numbers (########) in column A of worksheet ws_master. In my stesting, pvrid = "45072004", which matches the numeric value in cell A14.

Here is the data in column A of ws_master in which I'm seeking the match.

Is anyone able to identify my error?

 

[Ark68]

I forgot the data ...

WS 26-May-23.xlsx
	A
10	Record ID
11
12
13	45072003
14	45072004
15	45072018
16	45072019
17	45072011
18	45072012
19	45072013
20	45072021
21	45072004-1
22	45072018-1
23	45072019-1
24	45072009
25	45072010
26	45072004-1
27	45072018-2
28
29	45072005
30	45072017
31	45072006
32	45072007
33	45072008
34	45072014
35	45072015
36	45072016
37	45072020
38	45072001
39
40	45072002
41
42
43	Facility Maintenance Activities
Master

 

[Anthony47]

pvrid is defined as string, so CStr adds nothing to the situation
So I think you need to look carefully to your column A and pvrid and search for "hard to detect" differences. For example, are even those values that look like a number (eg 45072003) stored as string?

In general Application.WorksheetFunction.Match throws a run-time error if the match fails. Thus I suggest you use Application.Match, that returns Error(2042) as the result; something like this:

Dim rwprid As Variant           'watch As Variant
rwprid = Application.Match(CStr(pvrid), ws_master.Columns(1), 0)
If Not IsError(rwprid) Then
    'successful Match, run the other instructions:
    'your instruction
    'your instruction
    'your instruction
End If

Try to adapt to your situation

 

[Ark68]

Hi Anthony, I just caught the same thing about Cstr'ng a string. Kinda redundant.
In my data, all but those values that have a '-#' are indeed numbers. The others are text. So I think the problems is trying to match a text value to a number.
I adjusted my code as per your suggestion, and it threw the error code then just ended. So. No match I suppose.
Perhaps I should change all the data in Column A to a string? Not sure how to do that.

 

[Anthony47]

Try modifying how pvrid is generated:

    If psvcnum = 0 Then
        pvrid = nRID
    Else
        pvrid = trid & "-" & psvcnum
    End If

This requires pvrid be declared As Variant

 

[Ark68]

Hmmm, Excel isn't cooperating with us.
Still no match.

Private Sub UserForm_Initialize()
    Dim qw As String
    Dim tqw As String
    Dim nRID As Double
    Dim nsvc As Long, psvcnum As Long
    Dim evtf As String, psvcdiv As String
    Dim pvrid As Variant
    Dim rwprid As Variant
    Me.Caption = "TOURNAMENT SERVICE:  " & tcnt & " of " & ref
Stop
   
    trid = ridt 'rid (text)
    nRID = CDbl(trid) 'rid (number)
    nsvc = tsvcnum 'service (-#) number
    Debug.Print "Full Toun. RID: " & trid & "-" & nsvc
    
    'previous service division
    psvcnum = nsvc - 1
    If psvcnum = 0 Then
        pvrid = nRID
    Else
        pvrid = trid & "-" & psvcnum
    End If
    pdaEnd = Application.WorksheetFunction.Match("Facility Maintenance Activities", ws_master.Columns(1), 0) - 2
    'With ws_master.Range("A13:A" & pdaEnd)
    '    .NumberFormat = "Text"
    '    .Value = .Value
    'End With
    rwprid = Application.Match(CStr(pvrid), ws_master.Columns(1), 0)
    If Not IsError(rwprid) Then
        evtf = ws_master.Cells(rwprid, 5)
        psvcdiv = Split(Split(evtf, ")")(0), "(")(1)
        Me.lb_pdiv.Caption = psvcdiv
        
        cntsvc = 0

I did experiment with ...

With ws_master.Range("A13:A" & pdaEnd)
        .NumberFormat = "Text"
       .Value = .Value
 End With

using both "General" and "Text" numberformats but I don't think any of them changed the numbers to a string value.

 

[Anthony47]

I used your data in Column A with the following snippet:

psvcnum = 2                 '<<<< Change manually
nRid = 45072018             '<<< Change manually
trid = CStr(nRid)

Debug.Print ">>> psvcnum: " & psvcnum, "nRid: " & nRid, "trid: " & trid
    If psvcnum = 0 Then
        pvrid = nRid
    Else
        pvrid = trid & "-" & psvcnum
    End If
Debug.Print pvrid, TypeName(pvrid)
    rwprid = Application.Match(pvrid, ws_master.Columns(1), 0)
Debug.Print "rwprid: " & CStr(rwprid)
Debug.Print

I run the code changing manually the value assigned to psvcnum: 2 / 0 / 22

Debug.Print provided in the vba “Immediate window” the following debug information:

>>> psvcnum: 2              nRid: 45072018              trid: 45072018
45072018-2    String
rwprid: 21

>>> psvcnum: 0              nRid: 45072018              trid: 45072018
 45072018     Long
rwprid: 9

>>> psvcnum: 22             nRid: 45072018              trid: 45072018
45072018-22   String
rwprid: Errore 2042

With pvrid = 45072018-2 (String) the result of Match was 21
With pvrid = 45072018 (Long) the result of Match was 9
With pvrid = 45072018-22 (String) the result of Match was Error 2042

These results are in agreement with the content of column A


Try adding the same Debug.Print in your code as follows:

    trid = ridt 'rid (text)
    nRID = CDbl(trid) 'rid (number)
    nsvc = tsvcnum 'service (-#) number
    Debug.Print "Full Toun. RID: " & trid & "-" & nsvc
    
    'previous service division
    psvcnum = nsvc - 1
    If psvcnum = 0 Then
        pvrid = nRID
    Else
        pvrid = trid & "-" & psvcnum
    End If
    
Debug.Print pvrid, TypeName(pvrid)
    
    pdaEnd = Application.WorksheetFunction.Match("Facility Maintenance Activities", ws_master.Columns(1), 0) - 2
    'With ws_master.Range("A13:A" & pdaEnd)
    '    .NumberFormat = "Text"
    '    .Value = .Value
    'End With

Debug.Print "pdaEnd: " & pdaEnd

    rwprid = Application.Match(CStr(pvrid), ws_master.Columns(1), 0)
    
Debug.Print "rwprid: " & CStr(rwprid)
Debug.Print
    
    If Not IsError(rwprid) Then
        'etc etc

Then examine the log in the Immediate window and try to guess what doesn’t work; share the log and we will share the thoughts

 

[Ark68]

Hi Anthony, I really appreciate your continued support. Thank you.
I added your diagnostic lines, and although a different set of similar data, I'm running into the same issues.

Here is the 'log' from the first instance ...

Full Toun. RID: 45073009-1
 45073009     Double
pdaEnd: 64
rwprid: Error 2042

The data I'm using ...

WS 27-May-23.xlsx
	A
12
13	45073008
14	45073009
15	45073023
16	45073024
17	45073014
18	45073015
19	45073016
20	45073009-1
21	45073009-2
22	45073008-1
23	45073023-1
24	45073024-1
25	45073015-1
26	45073014-1
27	45073016-1
28	45073011
29	45073009-3
30	45073008-2
31	45073023-2
32	45073024-2
33	45073014-2
34	45073009-4
35	45073008-3
36	45073023-3
37	45073024-1
38
39	45073012
40	45073013
41	45073018
42	45073019
43	45073020
44	45073017
45	45073001
46	45073003
47	45073004
48	45073006
49	45073022
50	45073030
51	45073010
52	45073002
53
54	45073005
55
56	45073029
57	45073007
58	45073027
59	45073025
60	45073028
61	45073026
62	45073099
63	45073021
64
65
66	Facility Maintenance Activities
Master_WRK

 

[Anthony47]

Does the error happens both for Numbers and strings?

Can you show the debug.print that produced such output?

In a free area of your worksheet (I used I1:J1):
-in I1 TYPE (do not COPY from column A) the Number that failed to match (for example 45073009)
-in J1 set the formula =COUNTIF(A:A,I1)
Check that the result is 1 or higher; if it is 0 then doublecheck in column A the cell that seems to contain 45073009, and look for trailing or leading spaces; format the cell with 2 decimals and check that it display correctly; extend the decimal to 10 and check the number is always shown as an Integer (all decimals at 0); retype the whole Number. At any test check the result of the COUNTIF formula

 

[Ark68]

Does the error happens both for Numbers and strings?

It seems to, yes.

Can you show the debug.print that produced such output?

Here is the code, if this what you're referring to:

Private Sub UserForm_Initialize()
    Dim qw As String
    Dim tqw As String
    Dim nRID As Double
    Dim nsvc As Long, psvcnum As Long
    Dim evtf As String, psvcdiv As String
    Dim pvrid As Variant
    Dim rwprid As Variant
    Me.Caption = "TOURNAMENT SERVICE:  " & tcnt & " of " & ref
Stop
   
    trid = ridt 'rid (text)
    nRID = CDbl(trid) 'rid (number)
    nsvc = tsvcnum 'service (-#) number
    Debug.Print "Full Toun. RID: " & trid & "-" & nsvc
    
    'previous service division
    psvcnum = nsvc - 1
    If psvcnum = 0 Then
        pvrid = nRID
    Else
        pvrid = trid & "-" & psvcnum
    End If
    
    Debug.Print pvrid, TypeName(pvrid)
    
    pdaEnd = Application.WorksheetFunction.Match("Facility Maintenance Activities", ws_master.Columns(1), 0) - 2
    'With ws_master.Range("A13:A" & pdaEnd)
    '    .NumberFormat = "Text"
    '    .Value = .Value
    'End With
    
    Debug.Print "pdaEnd: " & pdaEnd
    
    rwprid = Application.Match(CStr(pvrid), ws_master.Columns(1), 0)
    
    Debug.Print "rwprid: " & CStr(rwprid)
    Debug.Print
    
    If Not IsError(rwprid) Then
        evtf = ws_master.Cells(rwprid, 5)
        psvcdiv = Split(Split(evtf, ")")(0), "(")(1)
        Me.lb_pdiv.Caption = psvcdiv

As far as your diagnostic suggestion,
By typing (no copy) 45073009 into a blank cell, the result of the formula was 1. There was a match.
By typing (no copy) 45073009-1 into a blank cell, the result of the formula was 1. There was a match.
By typing (no copy) 45073009-22 into a blank cell, the result of the formula was 0. There was no match.