If Statement ComboBox Value

[acarlson4]

I am using two userforms. One to input the purchase order (by item number). The other userform is to receive the order (by item number). In the receive userform, I created a combobox with a master list of item numbers as options. I would like to use an if then statement so that if the combobox value entered in the receive userform is equal to the item number on the order form from the order userform, that item will be marked as "COMPLETE".

I have tried redefining the combobox value as a variable and nothing happens.
I have also tried pasting the combobox value into a random cell and then checking that cell value against the order list. After it is marked complete, then I delete the pasted combobox value in the random cell. This DOES work. I would like to avoid this extra step if possible though.

Below is my code:

Private Sub cmdAdd_Click()
Dim ItemNoX As Integer


LastItemNo = Cells(Rows.Count, 4).End(xlUp).Row


For ItemNoX = 7 To LastItemNo
    If frmReceive.cboItem.Value = Worksheets("Purchase Order").Cells(ItemNoX, 4).Value Then
        Worksheets("Purchase Order").Cells(ItemNoX, 10).Value = "COMPLETE"
    End If
Next ItemNoX


End Sub

[SamT]

Not enough info to determine what you want, need, are trying to do, and what all objects you are using.

This code tries to Find the first occurrence of cboItem.value that occurs in column D on Worksheet "Purchase Order." If it succeeds, it sets the cell in the same row of column "J" to "COMPLETE". Note that the Variable "Me" refers to the UserForm that Command Button "cmdAdd" is in.

It assumes that there are no merged cells in Column "D". If there are Merged Cells, change the Range to ("D7:D1000") or ("D7:D" & LastItemNo). Using Range("D:D") is still much faster than iterating thru the Range one Row at a time.

BTW, you code can be made a little more efficient by adding "Exit Sub" before the "End If" line. Why keep looping after you found the Item number?

Private Sub cmdAdd_Click()
  Dim Found As Range
   
  Set Found = Find(Me.cboItem.Value, Worksheets("Purchase Order").Range("D:D"))
  If Not Found Is Nothing Then Found.Offset(0, 6).Value = "COMPLETE"
     
End Sub

[snb]

or

Private Sub cmdAdd_Click()
      on error resume next      
  Worksheets("Purchase Order").Columns(4).Find(cboItem.Value,,,1).offset(,6)="Complete"
End Sub

[SamT]

@ snb,
Nice

[acarlson4]

snb: That worked great for finding the first item occurrence. Is there any way to adjust it so that it can mark duplicate item numbers as "complete".

SamT: Essentially when the user places the order, he selects an item component number in a combobox which then pulls all sub components used in that item component number (from a separate sheet). These sub components may or may not be the same sub components for a different item component number. When the user receives the order, however, he receives the sub component numbers rather than the main item component number (which is out of my control). Therefore I need the combobox value (of the sub component) to find all occurrences in the purchase order sheet and mark as "complete". I know I will have to develop the logic further eventually to match order quantity, but I first need to tackle the find and "complete" logic.

Thank you for all your help!

[SamT]

I still don't know what you have to work with. Please lay it out in detail for us and refer to objects by their code name.

What I think you are telling us is:

The User has a paper Purchase Order on his desk and a Purchased UserForm, He fills the PO UserForm with the details from the Paper PO. This UF then fills a Worksheet with the ordered Items (numbers.)

The User also has a paper Items Received form on his desk. The Received UserForm has a list of all possible Item numbers. Each time the User selects an Item number from this master list, you need the PO Worksheet to be updated with "COMPLETE"

BTW, what has to happen when an order is short or back ordered? I'm assuming that the recieving clerk has dealt with overages and mis-delivered items.