How to make combobox and Textbox join together for application.match

[HYKE]

Hi There,

Can somebody help for a VBA code for combobox and textbox as reference for application.match?

This is the code I have for extracting the data from the worksheet to userform

Dim prodfound As Range
    With Worksheets("BPME").Range("G:G")
        Set prodfound = .Find(shptUpdate.Value)
        
        If prodfound Is Nothing Then
            MsgBox ("ITEM NOT FOUND!")
            shptUpdate.Value = ""
            Exit Sub
            
        Else
            With Range(prodfound.Address)
            frmUpdate.ductName.Text = .Offset(0, -3)
            frmUpdate.BLNo.Text = .Offset(0, 1)
            frmUpdate.sPlier.Text = .Offset(0, 3)
            frmUpdate.voiNum.Text = .Offset(0, 5)
            frmUpdate.qTy.Text = .Offset(0, 6)
            dateshptRec.Text = .Offset(0, 14)
            Mritr.Text = .Offset(0, 16)
            End With
        End If
     End With

where shptUpdate is a combobox looking for the partnumber from range ("G:G") and BLNo is a textbox extracting the serial number for the partnumber. Now, I have other sheet where I will have to update basing on the shptUpdate (combobox) and BLNo (textbox) data. The VBA code is the code that was provided to me here a year ago. The red ones is my addition that don't work.

Dim BpRow As Long
Dim bpPart As Long
Dim Bps As Worksheet
Set Bps = Worksheets("BPME")
    With Us
         r = Application.Match([COLOR=red]shptUpdate.Value & BlNo.value[/COLOR], .Range("G:G"), False)
        .Cells(r, 21).Value = shptRec.Value
        .Cells(r, 22).Value = jdePo.Value
        .Cells(r, 23).Value = ovNum.Value
        .Cells(r, 25).Value = recQty.Value
        .Cells(r, 26).Value = lNum.Value
        '.Cells(r, 26).Value = .Cells(r, 14).Value + Val(TextBox14.Value)
        '.Cells(r, 27).Value = .Cells(r, 15).Value + Val(TextBox15.Value)
        '.Cells(r, 28).Value = .Cells(r, 16).Value + Val(TextBox16.Value)
        .Cells(r, 32).Value = reMrks.Value
        shptRec.Value = ""
        jdePo.Value = ""
        ovNum.Value = ""
        recQty.Value = ""
        lNum.Value = ""
        reMrks.Value = ""
        'TextBox16.Value = ""
        'TextBox9.Value = ""
        'Range("a1").Select
       
    End With

The reason why I need to have two reference is because I have duplicate partnumbers but different serial numbers. I tried using the partnumber as my only reference but when there is a duplicate partnumber down the column, the one that gets updated is always the first on the list. I really need help here as I am almost done except for this. Thanks again for all of your help/assistance

HYKE

 

[HYKE]

anyone please!

 

[HYKE]

I hope I am not annoying he forum but I really need a help on this one....

 

[Norie]

Are you trying to use Match when the values you are looking for are in separate columns?

If you are you can't.

Why not use a find for the part number, check if the serial number in the same row matches the one you are looking for.

If it isn't search for the next occurrence of the part no, check the serial no again.

Repeat until you find a matching serial no or you've gone through all the part numbers.

How exactly to do that depends on how the data is setup.

 

[HYKE]

Hi Norie,

Will you kindly provide me a code for this suggestion of yours?

Why not use a find for the part number, check if the serial number in the same row matches the one you are looking for.

If it isn't search for the next occurrence of the part no, check the serial no again.

Repeat until you find a matching serial no or you've gone through all the part numbers.

The part number is in G column while the serial number in H.

Thank you for your time.

HYKE

 

[Norie]

HYKE

I think a little more information would be needed.

Are the serial numbers in a contiguous list?

eg in H10:H20 you have the same serial number and in the corresponding rows of column G you have the part numbers.

 

[HYKE]

yes, the list are on columns H3:H100 and G3:G100.