Match Function in Vba

[charly1]

Hi all
I am using a dynamic textbox value to populate other textboxes in a user form, by way of the match function. There seems to be a problem, and though I think I get what the issue is i cant work out a solution. The line I think that causes issues has been highlighted in red. The problem seems to be whenever the lookup textbox contains a number as opposed to letters. If the textbox lookup, contains letters, everything runs ok and all the other textboxes are populated with the correct matches however in my case most of the time the textbook contains a number, and in that case the below code doesn't populate any of the other textboxes, even though there is a matching cell in the table with the same number.

Private Sub TB_Hidden1_Change()
   Dim BookRow As Long
    Dim BookRange As Range

    ' Turn off default error handling so Excel does not display
    ' an error if the record number is not found
    On Error Resume Next
        
        ' Find the row in the table that the record is in
       [COLOR=rgb(226, 80, 65)] BookRow = Application.Match((TB_Hidden1.Value), Range("Table1[Column1]"), 0)[/COLOR]
        
        ' Set RecordRange to the first cell in the found record
        Set BookRange = Range("Table1").Cells(1, 1).Offset(BookRow - 1, 0)

        ' If an erro has occured i.e the record number was not found
        If Err.Number <> 0 Then
    
            ErrorLabel.Visible = True
            On Error GoTo 0
            Exit Sub
        
        End If
    
    ' Turn default error handling back on (Let Excel handle errors from now on)
    On Error GoTo 0
    
    ' If the code gets to here the record number was found
    ' Hide the error message 'Not Found'
    ErrorLabel.Visible = False
  
    ' and populate the form fields with the record's data
    TB_D_Book.Value = BookRange(1, 1).Offset(0, 4).Value
 TB_D_Vol.Value = BookRange(1, 1).Offset(0, 5).Value
  CB_D_Subject1.Value = BookRange(1, 1).Offset(0, 6).Value
   CB_D_Subject2.Value = BookRange(1, 1).Offset(0, 7).Value
    TB_D_Subject3.Value = BookRange(1, 1).Offset(0, 8).Value
     TB_D_Summary.Value = BookRange(1, 1).Offset(0, 12).Value
      CB_D_Artist.Value = BookRange(1, 1).Offset(0, 13).Value
      Lbl_Artist_AsTB.Caption = BookRange(1, 1).Offset(0, 14).Value
       TB_D_Inside.Value = BookRange(1, 1).Offset(0, 1).Value
        TB_D_Column.Value = BookRange(1, 1).Offset(0, 2).Value
         TB_D_Shelf.Value = BookRange(1, 1).Offset(0, 3).Value
End Sub

 

[Akuini]

Application.Match is sensitive to data type whether it's a string or a number. So, you need to check it first. Try:

   Dim BookRow As Long
    Dim BookRange As Range

    Dim x As Variant
    ' Turn off default error handling so Excel does not display
    ' an error if the record number is not found
    On Error Resume Next
        
        ' Find the row in the table that the record is in
        x = TB_Hidden1.Value
        If IsNumeric(x) Then x = CDbl(x)
        BookRow = Application.Match((x), Range("Table1[Column1]"), 0)
        
        ' Set RecordRange to the first cell in the found record
        Set BookRange = Range("Table1").Cells(1, 1).Offset(BookRow - 1, 0)

 

[charly1]

Works perfectly.

Thanks again. [I'm nervous I'm gonna start getting on your nerves already, the amount you've helped me )

 

[Akuini]

Works perfectly.

Thanks again. [I'm nervous I'm gonna start getting on your nerves already, the amount you've helped me )

Hahaha, don't worry, my friend. Writing VBA code is just a hobby of mine. In fact, I feel less stressed when I'm writing code.