Find Record by ID and transfer whole row data to multiple textboxes based on selection in list box VBA

[Melimob]

Hi, I hope I am going to be able to explain this and apologies in advance for my horrendous code but am new to all this..

ok so i have a listbox on the top part of the userform which shows search results from Valuations worksheet.
in the bottom part of the userform there are multipages representing 4 worksheets; Valuations, Listings, Sales, Exchanges.
All records start on Valuations worksheet and could be on the Listings, Sales, Exchanges all tied together with a Unique ID.
Currently user selects a row in the listbox and the below code should:

1) populate all textboxes across all 4 pages with the 'constant data' shown as 'x' *This part works fine*

what doesn't currently work is the 'y, z, a' or else part..
2) it should look for the UID in the other 3 worksheets and if found, return the row/column data to the relevant multipage textbox as mapped out below.
3) if it doesn't find the UID on the other worksheets, it should just leave those textboxes blank ready for input

I did get point 2 to work before I added the else but with the 'else' 2 nor 3 seems to work?

Many thanks in advance > this has taken me hourssssssssssssss!
any advice appreciated!

Private Sub lstSearch_Click()


'dim the variables
Dim i As Integer
Dim x As Integer
Dim y As Integer
Dim z As Integer
Dim a As Integer


Dim ws As Worksheet
Dim wsl As Worksheet
Dim wss As Worksheet
Dim wse As Worksheet




Dim wsLR As Long


'find the selected list item
i = Me.lstSearch.ListIndex
Me.lstSearch.Selected(i) = True


'send UID to datasheet


Sheets("Valuations").Range("v6").Value = Me.lstSearch.Column(0, i)


Set ws = ThisWorkbook.Sheets("Valuations")
Set wsl = ThisWorkbook.Sheets("Listings")
Set wss = ThisWorkbook.Sheets("Sales")
Set wse = ThisWorkbook.Sheets("Exchanges")




 wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row


For x = 13 To wsLR
    
    If ws.Cells(x, 1) = Sheets("Valuations").Range("v6").Value Then
        
        'constants from Valuations sheets sent to all 4 multipages
        
        Me.tbUIDVal = ws.Cells(x, "A").Text
        Me.tbUIDList = ws.Cells(x, "A").Text
        Me.tbUIDSales = ws.Cells(x, "A").Text
        Me.tbUIDExc = ws.Cells(x, "A").Text
        
        Me.cbxOfficeVal = ws.Cells(x, "B")
        Me.cbxOfficeList = ws.Cells(x, "B")
        Me.cbxOffSales = ws.Cells(x, "B")
        Me.cbxOffDtlExc = ws.Cells(x, "B")
        
        Me.tbHouseVal = ws.Cells(x, "E")
        Me.tbHouseList = ws.Cells(x, "E")
        Me.tbHouseSales = ws.Cells(x, "E")
        Me.tbHouseExc = ws.Cells(x, "E")
        
        Me.tbStreetVal = ws.Cells(x, "F")
        Me.tbStreetList = ws.Cells(x, "F")
        Me.tbStreetSales = ws.Cells(x, "F")
        Me.tbStreetExc = ws.Cells(x, "F")
        
        Me.tbCityVal = ws.Cells(x, "G")
        Me.tbCityList = ws.Cells(x, "G")
        Me.tbCitySales = ws.Cells(x, "G")
        Me.tbCityExc = ws.Cells(x, "G")
        
        Me.tbPostCodeVal = ws.Cells(x, "H")
        Me.tbPostCodeList = ws.Cells(x, "H")
        Me.tbPostCodeSales = ws.Cells(x, "H")
        Me.tbPostCodeExc = ws.Cells(x, "H")
        
        Me.tbValueAmountVal = ws.Cells(x, "J")
        Me.tbValAmntList = ws.Cells(x, "J")
        Me.tbValueAmntSales = ws.Cells(x, "J")
        Me.tbValAmntExc = ws.Cells(x, "J")
        
        Me.tbVendorVal = ws.Cells(x, "I")
        Me.tbVendorList = ws.Cells(x, "I")
        Me.tbVendorSales = ws.Cells(x, "I")
        Me.tbVendorExc = ws.Cells(x, "I")
        
        
        
    'cells on valuations worksheet that should only be sent to Valuations multipage
        Me.tbDateVal = ws.Cells(x, "C")
        Me.cbxValuer = ws.Cells(x, "D")
        Me.chbValLetter = ws.Cells(x, "K")
        Me.cbxEnqSourceVal = ws.Cells(x, "L")
        Me.cbxDataSourceVal = ws.Cells(x, "M")
        Me.tbNotesVal = ws.Cells(x, "N")
        


     End If
Next x
   
   
   For y = 13 To wsLR
     If wsl.Cells(y, 1) = Sheets("Valuations").Range("v6").Value Then
    
        'get stats from Listings worksheet for addtional fields to add to listings page if record found
        
        Me.tbDateList = wsl.Cells(y, "C")
        Me.cbxLister = wsl.Cells(y, "D")
        Me.chbBoardList = wsl.Cells(y, "M")
        Me.chbPM2 = wsl.Cells(y, "N")
        Me.tbPriceList = wsl.Cells(y, "J")
        Me.tbPriceSales = ws.Cells(y, "J")
        Me.tbFeeList = wsl.Cells(y, "K")
        Me.cbxStatusList = wsl.Cells(y, "O")
        Me.tbNotesList = wsl.Cells(y, "L")
        
        'Comes from Listing worksheet to Exchange Multipage
         Me.tbPriceExc = wsl.Cells(y, "J")
         Me.cbxUpdateListStatusExc = wsl.Cells(y, "O")
        
    
    Else
        Me.tbDateList = ""
        Me.cbxLister = ""
        Me.chbBoardList = ""
        Me.chbPM2 = ""
        Me.tbPriceList = ""
        Me.tbPriceSales = ""
        Me.tbFeeList = ""
        Me.cbxStatusList = ""
        Me.tbNotesList = ""
        
        Me.tbPriceExc = ""
        Me.cbxUpdateListStatusExc = ""
        
        
 End If
 Next y
 
    For z = 13 To wsLR
     If wss.Cells(z, 1) = Sheets("Valuations").Range("v6").Value Then
    
        'get stats from Sales worksheet for addtional fields to add to Sales page if record found
        
        Me.tbDateSales = wss.Cells(z, "C")
        Me.cbxNegSales = wss.Cells(z, "D")
        Me.chbSale = wss.Cells(z, "K")
        Me.chbAbort = wss.Cells(z, "K")
        Me.tbPurchaserSales = wss.Cells(z, "I")
        Me.tbFeeSales = wss.Cells(z, "J")
        Me.tbNotesSales = wss.Cells(z, "L")
        
        'comes from Sales worksheet to Exchange multipage
        Me.tbPurchaserExc = wss.Cells(z, "I")
        
    Else
        Me.tbDateSales = ""
        Me.cbxNegSales = ""
        Me.chbSale = ""
        Me.chbAbort = ""
        Me.tbPurchaserSales = ""
        Me.tbFeeSales = ""
        Me.tbNotesSales = ""
        
        Me.tbPurchaserExc = ""
 
   End If
 Next z
 
 For a = 13 To wsLR
 
 If wse.Cells(a, 1) = Sheets("Valuations").Range("v6").Value Then
        Me.tbDateExc = wse.Cells(a, "C")
        Me.tbDateCompExc = wse.Cells(a, "K")
        Me.tbDatePaidExc = wse.Cells(a, "L")
        Me.chbOffSplitExc = wse.Cells(a, "N")
        Me.chbIntheBookExc = wse.Cells(a, "M")
        Me.tbListByExc = wse.Cells(a, "H")
        Me.tbSoldByExc = wse.Cells(a, "I")
        Me.tbFeeExc = wse.Cells(a, "J")
        
 Else
        Me.tbDateExc = ""
        Me.tbDateCompExc = ""
        Me.tbDatePaidExc = ""
        Me.chbOffSplitExc = ""
        Me.chbIntheBookExc = ""
        Me.tbListByExc = ""
        Me.tbSoldByExc = ""
        Me.tbFeeExc = ""
       
 End If
 Next a
 
 Exit Sub
    
End Sub

 

[dmt32]

You need to test what is being passed to Variable Search - either use Debug.Print or place Msgbox after variable is intialized

[h=2]'search UID
Search = Me.lstSearch.Column(0, i)
[/h]Msgbox Search

If this shows correct value then problem further in the code.

Dave