Index, match function doesn't recognize texted format cell

Question

I have TextBox which contain number over 15 digits. When I copy the textbox value into excel cell, starting from digit 15th excel will change it zero number. This is Excel's way if cell format as number. I change the cell format from number to text, and input manually the number, and its work.

But when I use Index, Match function on macros, it doesn't work and I get

Run-time error 1004 "Unable to get match property of the WorksheetFunction class

This is the code I use for index, match function

Dim b As Double

b = TxtCvNo.Text

With Application.WorksheetFunction    
    If .CountIf(Sheet2.Range("G:G"), b) = 1 Then    
        TxtCVId.Text = .Index(Sheet2.Range("A:A"), .Match((b), Sheet2.Range("G:G"), 0))     
    Else

b is the TextBox value.

Match function doesn't recognize b as this value were input as 'text' in Excel cell

How can I copy the textbox value into cell as text to avoid digit 15th, 16th and so on change to zero. How can I fix the index, match function after it.

this screen shot of my Data. I use Index and Match to identify whether the coupon 1,2,3 has been use as coupon ref, and then tell me the ID Respondent use that Coupon. screen shot data

yes, It work now. But I didn't use res as you have in your code. Thanks alot. — Hepk1211
Then please mark as "ANSWER" by clicking on the check-mark next to my answer, it will turn green — Shai Rado
why the match function sometime is work but sometime is not? — Hepk1211
it depends how the values you are looking for formatted, in your case with very long numbers stored as text, it can get tricky. You can edit your post and add a screen shot of the data you are trying toMatch with — Shai Rado

Shai Rado Shai Rado · Accepted Answer · 2017-03-29T17:41:58

Try the code below (explanation inside the code comments):

Option Explicit

Sub IndexMatchConv()

Dim b As String
Dim Res As Variant

' for my test format and set a value with 21 digits as Text in cell "G2"
Sheet2.Range("G2").NumberFormat = "@" ' format as Text
Sheet2.Range("G2").Value = "123456789112233445566"
b = Sheet2.Range("G2").Value
MsgBox Len(b) ' <-- result 21

With Application.WorksheetFunction
    Res = .CountIf(Sheet2.Range("G:G"), b)
    If .CountIf(Sheet2.Range("G:G"), b) = 1 Then
        Res = .Match((b), Sheet2.Range("G:G"), 0) < --Res = 2
        'TxtCVId.Text = .Index(Sheet2.Range("A:A"), .Match((b), Sheet2.Range("G:G"), 0))
    Else
        ' do something
    End If
End With

End Sub

Index, match function doesn't recognize texted format cell

1 Answers