MarcinOz
01-13-2020, 09:16 PM
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
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