Userform Data Mismatch issues

[Carlit007]

I Have created a user form that auto populate fields 2-11 with data from the range on sheet1 ("Data12").

all of this is based on the Serial number inputted into form field number #1 which is a dropdown list that uses the range in colum 1 of the sheet which holds all the other data

the only problem I am having is data mismatch because the serial number is not always just numbers sometimes there's a mix of letters and numbers.
I keep getting the error runtime error 1004 is there any way to make it so that my variable is flexible to be numerical value as well as mixed

I have tried both the Vlookup method and index and match but keep running into the same issues

below is a snipped of the code that I am using any help with this would be deeply appreciated

Private Sub CBserial_Change()



Dim Findrow As Long
Dim SerialNumber As Variant

SerialNumber = Me.CBserial.Text   'this holds the Serial Number data which could be both a number or mixed value

Findrow = WorksheetFunction.Match(SerialNumber, Sheets("Data12").Range("A:A"), 0)

            TextBox1.Text = Cells(Findrow, 2) 'LIN
            TextBox2.Text = Cells(Findrow, 3) 'MATERIAL #
            TextBox3.Text = Cells(Findrow, 4) 'ADMIN #
            TextBox4.Text = Cells(Findrow, 5) 'DESCRYPTION
            TextBox5.Text = Cells(Findrow, 6) 'LOCATION

End Sub

 

[DanteAmor]

Try this

Private Sub CBserial_Change()
  Dim f As Range
  TextBox1.Value = ""
  TextBox2.Value = ""
  TextBox3.Value = ""
  TextBox4.Value = ""
  TextBox5.Value = ""
  If CBserial.ListIndex > -1 Then
    With Sheets("Data12")
      Set f = .Range("A:A").Find(CBserial, , xlValues, xlWhole)
      If Not f Is Nothing Then
        TextBox1.Value = .Cells(f.Row, 2) 'LIN
        TextBox2.Value = .Cells(f.Row, 3) 'MATERIAL #
        TextBox3.Value = .Cells(f.Row, 4) 'ADMIN #
        TextBox4.Value = .Cells(f.Row, 5) 'DESCRYPTION
        TextBox5.Value = .Cells(f.Row, 6) 'LOCATION
      Else
        MsgBox "Dont exists"
      End If
    End With
  End If
End Sub

 

[Carlit007]

WOW Thanks so much Dante!!! that worked like a charm!!!

That was such a quick work to this problem that been making me pull my hair I have much to learn with this different method you showed me .

I have a button on the user form that updates the data on the spreadsheet for example the location of an item that constantly changes
I reverse engineered some of the info on the code you sent me and It worked flawlessly below is the code for the Update button now I just need to learn how to work a button that lets me add new data not in the sheet

Thanks again for your expertise

Private Sub UPdateButton1_Click()

Dim f As Range

 
If CBserial.ListIndex > -1 Then
With Sheets("Data12")
Set f = .Range("A:A").Find(CBserial, , xlValues, xlWhole)
      If Not f Is Nothing Then

Cells(f.Row, 2) = TextBox1.Value  'LIN
Cells(f.Row, 3) = TextBox2.Value 'MATERIAL #
Cells(f.Row, 4) = TextBox3.Value 'ADMIN #
Cells(f.Row, 5) = TextBox4.Value 'DESCRYPTION
Cells(f.Row, 6) = TextBox5.Value 'SECTION
Cells(f.Row, 7) = TextBox6.Value 'ROOM
Cells(f.Row, 8) = TextBox7.Value 'DESK/SHELF
Cells(f.Row, 9) = TextBox8.Value 'LOCATION
Cells(f.Row, 10) = TextBox9.Value 'SLOC
Cells(f.Row, 11) = TextBox10.Value 'LOCATION DETAIL
Cells(f.Row, 12) = TextBox11.Value  'signed by:


Else
MsgBox "Dont exists"
End If
End With
End If
End Sub

 

[DanteAmor]

You missed the dot

.Cells(f.Row, 2) = TextBox1.Value  'LIN

Your compacted code.

Private Sub UPdateButton1_Click()
  Dim f As Range, i As Long
  If CBserial.ListIndex > -1 Then
    With Sheets("Data12")
      Set f = .Range("A:A").Find(CBserial, , xlValues, xlWhole)
      If Not f Is Nothing Then
        For i = 2 To 12
          .Cells(f.Row, i) = Controls("TextBox" & i - 1)
        Next
      Else
        MsgBox "Dont exists"
      End If
    End With
  End If
End Sub

Example to add.

Private Sub AddButton1_Click()
  Dim lr As Long
  With Sheets("Data12")
    lr = .Range("A" & Rows.Count).End(3).Row + 1
    .Range("A" & lr).Value = "data in column A"
    .Range("B" & lr).Value = "data in column B"
    .Range("C" & lr).Value = "data in column C"
    '...etc
  End With
End Sub