Search which cell contains a specific text and mod

问题:

I am looking for a solution for following problem, and can't solve it at this time:

In Sheet1 I have my product order sheet. Column A is the product code, column B is the ordered quantity.

In Sheet2, the customer is selecting several variables, which end in a concatenated tag of the selected variables, like for example: A1 - the customer can choose from a,b or c (value validation through dropdown), A2 - the customer can choose from 1, 2 or 3 and A3 the customer can choose between red, blue or green. Then in value A4 the customer chooses the desired quantity. In a hidden cell, let's say A5 we concatenate the choices of variables and get the tag a2blue, for example, and we need to find WHICH product from sheet 1 has the tag a2blue, and change its quantity to 5, for example.

Now back to sheet1, I want to create a column C with the possible tags for a product.

For example productX can have the tag "a2blue,a3blue,b1blue". I want to create a formula which searches which product in sheet 1 matches the customer's tag and add the quantity. Let's say we found in sheet 1 that on line 9 there's the product code ProductX, which in column C has the tags "a2blue,a3blue,b1blue" (Attention, a product should be able to have multiple tags, therefore exact search is not possible, just a search if the text is contained). For this product, I want to change the quantity to 5.

Sure this can be also solved by VBA, but in VBA I'm newby so would need extra support.

Image 1: At B4 and B5, for example, the customer chooses Concrete and Bitumen. This will later on sheet2 generate some "tags" needed for different parts of the system.

Image 2: Snapshot of sheet 2. Cell B7 shows that based on the customer's selection from sheet1, as end anchor item we need a product which has the tag "Concretebitumenend". The quantity was already calculated in C7. Based on the tag Concretebitumenend (tag in Sheet2 cell B7, which needs 2 pieces according to C7), when I click Calculate on sheet1, it should find which product in Sheet1 column H has that tag among the listed tags. For example, in this case H29 does NOT fit, as it does not have the tag Concretebitumenend, so the right row should be found and then it should get the desired quantity in col E.

Thx for your support!

回答1:

Solution found:

Sub proof()
'
' proof Macro
'
    n2 = 26
    n1 = 489

    For i = 29 To 489
    Cells(i, 5) = 0
    Next i

    For i = 1 To n2
        For j = 29 To n1

        a = UCase(Sheets("sheet2").Cells(i, 2))
        b = UCase(Sheets("sheet1").Cells(j, 8))
        If a = "" Then GoTo 10
        If (a <> "" And b <> "") And InStr(b, a) Then
        'Stop
        Sheets("sheet1").Cells(j, 5) = Sheets("sheet1").Cells(j, 5) + Sheets("sheet2").Cells(i, 3)

        End If


        Next j
10  Next i


'
End Sub