Autofill Userform TextBox with data on sheets

[liaaa]

Good morning,
For Project Name and No Contract I want it autofill based on NIP that I have choose. This is my code and its not working

Private Sub CmbNIP_Change()
Dim i As Long, LastRow As Long, ws As Worksheet
Set ws = Sheets("List Proyek")
LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To LastRow
If Val(Me.CmbNIP.Value) = ws.Cells(i, "A") Then
MsgBox Me.CmbNIP.Value
Me.txtPROJECTNAME = ws.Cells(i, "B").Value
Me.txtNOCONTRACT = ws.Cells(i, "C").Value
End If
Next i
End Sub


This is the data on sheet List Proyek

Thank you for your help

 

[dmt32]

Hi

Welcome to forum

Looking at your data, you are searching for an Alpha Numeric value in the Column yet you have wrapped your combobox in the VAL function which will stop reading your search string at the first character that it does not recognize as part of a number. Also, your code is searching values case sensitive & maybe better to change this to make it case insensitive.

See if this update to your code now does what you want

Private Sub CmbNIP_Change()
    Dim i           As Long, LastRow As Long
    Dim ws          As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("List Proyek")
    LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    
    For i = 2 To LastRow
        If UCase(Me.CmbNIP.Value) = UCase(ws.Cells(i, "A").Value) Then
            Me.txtPROJECTNAME = ws.Cells(i, "B").Text
            Me.txtNOCONTRACT = ws.Cells(i, "C").Text
        End If
    Next i
End Sub

Note: that I have used the Range.Text property to return the search result to the controls. Using this property returns what you see in the cell (its format like a date or currency) rather than its underlying value.

Another observation it looks like you are using an Excel Table yet writing code for a Range.

Tables are powerful tools & make things much easier in so many ways especially, when writing code to access the data.

Here is one guide that you may find helpful in this respect: VBA Tables and ListObjects - Excel Off The Grid

Finally for future, when sharing data here, forum will find it more helpful if you use MRExcel Addin XL2BB - Excel Range to BBCode
to post copy of your worksheet rather than an image.



Dave

 

[liaaa]

Thank you very much

 

[dmt32]

Thank you very much

most welcome - appreciate your feedback

Dave