#1
|
|||
|
|||
UserForm VLookUp
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 Last edited by Pecoflyer; 03-02-2019 at 01:30 AM. Reason: Moved to correct sub forum |
#2
|
|||
|
|||
You will get better response(s) if you attach a sample workbook (not pictures of your sheets) with just enough data for us to work with.
Why the 'object required error' on the second line---> VBA considers SOL to be a variable not a worksheet. Using Option Explicit the VBA would have indicated this before running the macro. As-is, should be Sheets("SOL"). I suspect you'll get another error on that line because there is no bottom row for the CountIf range. For loading those text boxes and writing back to the SOL sheet there are different ways, my personal preference is using the Range.Find Method to locate Me.Shop_TB2.value, then using offset. Writing to the "Tool Room" sheet... I imagine Tool Maker is Me.Name_TB2, Tool Number is Me.PartNum_TB2, Date Completed is Me.Complete_TB2, but don't know what Hours is, nor where any of these should be written on the sheet. |
#3
|
||||
|
||||
Hi and welcome
please post in the correct subforum in the future. I moved it for you this time Thank you
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#4
|
|||
|
|||
The Tool Maker is ToolMaker_CB, tool number is ToolNumber_TB date completed is Complete_TB. My file is too big to send as an attachment. I have checked my sheet code and textbox name and both are correct.
|
#5
|
|||
|
|||
Quote:
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 |
#6
|
|||
|
|||
That works great. What command would I assign to the save button for the userform to replace the data entered if it was changed.
|
#7
|
|||
|
|||
Haven't Fluff, bakerman or 6StringJazzer given you that yet ?
Would be a waste of time if you've already got it. |
#8
|
|||
|
|||
no, I have gotten it to add but it doesn't replace it.
|
#9
|
|||
|
|||
Most every Excel forum on the net has specific rules regarding cross posting.
I don't believe this forum actually has a written rule, but courtesy is always appreciated. Have a read of this for an understanding of why it is important to let everyone know the situation: https://www.excelguru.ca/content.php?184 As stated in post #5 Quote:
Code:
If Not fndRng Is Nothing Then With Me fndRng.Offset(, 1) = .Date_TB2 fndRng.Offset(, 2) = .Name_TB2 fndRng.Offset(, 3) = .Area_TB2 fndRng.Offset(, 4) = .Account_TB2 fndRng.Offset(, 5) = .PartNum_TB2 fndRng.Offset(, 6) = .PartName_TB2 fndRng.Offset(, 7) = .Quantity_TB2 fndRng.Offset(, 8) = .RequestedDate_TB2 fndRng.Offset(, 9) = .Complete_TB2 fndRng.Offset(, 10) = .Build_TB2 End With End If |
Thread Tools | |
Display Modes | |
|
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 |