Consulting

Results 1 to 3 of 3

Thread: Copy userform info to worksheet row based on 2 criteria

  1. #1
    VBAX Newbie
    Joined
    Jan 2020
    Posts
    2
    Location

    Copy userform info to worksheet row based on 2 criteria

    Hi all,

    Working on an inventory register & going great so far, my problem is with my stock update userform, here the user enters any stock received. They use the form to find the original purchase order (PO Number) using the "Find Order" command, if the PO number is found it returns the first row of information back to the UserForm, the last 2 userform textboxes are used to input the amount received (amount outstanding is auto calculated) then "Update Order" command button. My code works great, however the issue I have is if there are multiple lines to one purchase order, which is very often, I need a second criteria e.g. "Our Code" to find the exact row on the "Orders" worksheet to paste the updated information, see worksheet example attached. As mentioned this code works:
    Private Sub CommandButton1_Click()
    Set findit = Sheet3.Range("B:B").Find(What:=Product2.Value)
    Sheet3.Unprotect Password:="000"
    Application.ScreenUpdating = False
    If Trim(Me.Product2.Value) = "" Then
    Me.Product2.SetFocus
    MsgBox ("Can't proceed - PO not found?")
    Exit Sub
    End If
    With Sheet3
    
    .Cells(findit.Row, 9).Value = Product7.Value
    .Cells(findit.Row, 10).Value = Product8.Value
    
    
    Sheet4.Protect Password:="000"
    End With
    MsgBox "Stock Updated" End Sub


    I just need
    the code to find the correct row by matching the PO number in column "B" & then the "Our Code" column "E" on the "Orders" worksheet, then copy the last 2 UserForm textboxes to the corresponding collumns on the "Orders" sheets ("I" & "J" respectively). Thanks for any guidance guys - Marco


    Attached Files Attached Files

  2. #2
    VBAX Newbie
    Joined
    Jan 2020
    Posts
    2
    Location
    Update: Ended up using 2 comboboxes, one to filter the PO numbers then the second to display all items from that PO number. Thanks anyway guys! - Marco

  3. #3
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    479
    Location
    Hi MarcinOz!
    Please refer to the attachment for the solution of the first problem.


    --Okami
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •