UserForm VLookUp

NoSparks · #5 03-04-2019, 09:03 AM

Quote:

My file is too big to send as an attachment.

I won't be mocking up a sample file to test anything with as I'd just be guessing at the data layout and user form you have.
You can decide whether or not that's necessary.

Something along the lines of this (untested) should load the user form,
and writing to the sheet can be done in a similar fashion with things inside the With-End With being swapped the other way round.

Code:

Private Sub Shop_TB2_AfterUpdate()
    Dim findString As String
    Dim fndRng As Range
    
findString = Me.Shop_TB2.Value
' change to correct range for find
Set fndRng = Sheets("SOL").Range("A:A").Find(What:=findString, LookIn:=xlValues, _
    LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

If Not fndRng Is Nothing Then
    'MsgBox findString & " was found at  " & fndRng.Address
    With Me
        .Date_TB2 = fndRng.Offset(, 1)
        .Name_TB2 = fndRng.Offset(, 2)
        .Area_TB2 = fndRng.Offset(, 3)
        .Account_TB2 = fndRng.Offset(, 4)
        .PartNum_TB2 = fndRng.Offset(, 5)
        .PartName_TB2 = fndRng.Offset(, 6)
        .Quantity_TB2 = fndRng.Offset(, 7)
        .RequestedDate_TB2 = fndRng.Offset(, 8)
        .Complete_TB2 = fndRng.Offset(, 9)
        .Build_TB2 = fndRng.Offset(, 10)
    End With
Else
    MsgBox findString & "  Is An Incorrect Order Number"
    Me.Shop_TB2.Value = ""
    Exit Sub
End If
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I return Vlookup only if Specific Criteria is met in 1 column of the Vlookup Array	EcommDOC	Excel	7	01-22-2018 11:00 AM
Help with UserForm	walber	Excel Programming	3	01-30-2017 12:12 AM
Userform calls other userform, then populate worksheet	Lehoi	Excel Programming	0	02-03-2016 02:58 PM
VBA Code in a UserForm module to delete a Command Button which opens the userform	Simoninparis	Word VBA	2	09-21-2014 03:50 AM
Is it possible to take an input from a UserForm in one document to a UserForm in a do	BoringDavid	Word VBA	5	05-09-2014 09:08 AM