Hi all,
I am trying to code a function into a userform whereby when someone enters an account number (accountnumber_Change()) it searches a worksheet for that account number and if found pulls the order number requirement (which is in the cell next to it) from that worksheet and displays it in the userform.
I have code that is working but it searches a worksheet in the same workbook as the userform. I am wanting to change it so it searches a worksheet in another workbook.
The second workbook I have is called 'Authorisation_Log.xlsx' and the worksheet is called 'Order Format'
Any help is greatly appreciated.
Private Sub AccountNumber_Change()
'loop thru account numbers and find order number format
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Order Format")
wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row
For x = 2 To wsLR
If ws.Cells(x, 1) = Me.AccountNumber Then
'get order number
Me.OrdeRequirement = ws.Cells(x, "B")
Exit Sub
End If
Next x
End Sub
Bookmarks