UserForm VLookUp

ChettoDust · #1 03-01-2019, 03:16 PM

Hello all,

I have created a userform to pull data from a worksheet to make changes then add the new data back into the worksheet. I am getting an 'object required error' on the second line
(If WorksheetFunction). My goal is to have tool makers be able to add there info into the form and hit save so that the new information is added back into the worksheet and also the information the tool maker added be saved to a second worksheet. My code is below:

The sheet I'm pulling data from is named "SOL"
The sheet I want all the new data to go back into "SOL" then only the data that the tool maker entered go to "Tool Room"

The only info the tool maker is adding is: Tool Maker, Tool Number, Hours, Date Completed

Code:

 
Private Sub Shop_TB2_AfterUpdate()
If WorksheetFunction.CountIf(SOL.Range("A8:A"), Me.Shop_TB2.Value) = 0 Then
MsgBox "Incorrect Order Number"
Me.Shop_TB2.Value = ""
Exit Sub
End If
With Me
.Date_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 2, 0)
.Name_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 3, 0)
.Area_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 4, 0)
.Account_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 5, 0)
.PartNum_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 6, 0)
.PartName_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 7, 0)
.Quantity_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 8, 0)
.RequestedDate_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 9, 0)
.Complete_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 10, 0)
.Build_TB2 = Application.WorksheetFunction.VLookup(CLng(Me.Shop_TB2), SOL.Range("Lookup"), 11, 0)
End With
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