搜索哪个单元格包含特定文本并修改偏移单元格 [英] Search which cell contains a specific text and modify an offset cell
问题描述
我正在寻找解决以下问题的解决方案，目前无法解决问题：
在Sheet1中，我有我的产品订单。列A是产品代码，列B是订购数量。
在Sheet2中，客户正在选择多个变量，其结尾在所选变量的连接标签中，例如：A1 - 客户可以从a，b或c（通过下拉列表的值验证）中选择，A2 - 客户可以从1,2或3中选择，A3可以选择红色，蓝色或绿色。然后在值A4中，客户选择所需数量。在隐藏的单元格中，我们假设A5连接变量的选择，并获得标签a2blue，例如，我们需要从表格1中找到具有标签a2blue的WHICH产品，并将其数量更改为5。 p>
现在回到sheet1，我想用产品的可能标签创建一个列C。
例如productX可以有标签a2blue，a3blue，b1blue。我想创建一个公式，搜索表1中的哪个产品与客户的标签匹配并添加数量。我们在表1中发现，在第9行中有9个产品代码ProductX，其中C列有标签a2blue，a3blue，b1blue（注意，一个产品应该能够有多个标签，因此精确的搜索是不可能的，只需搜索文本是否包含）。对于该产品，我想将数量更改为5。
当然这也可以通过VBA解决，但是在VBA中，我是新手，所以需要额外的支持。
图1：在B4和B5，客户选择混凝土和沥青。这将在以后的sheet2生成系统的不同部分所需的一些标签。
图2：表2的快照。单元格B7显示，根据客户从sheet1的选择，作为终端锚点，我们需要一个具有标签Concretebitumenend的产品。数量已经在C7中计算。
根据标签Concretebitumenend（Sheet2单元格B7中的标签，根据C7需要2件），当我单击Sheet1上的Calculate时，应该找到Sheet1列H中的哪个产品在列出的标签中有该标签。例如，在这种情况下，H29不适合，因为它没有标签Concretebitumenend，所以应该找到正确的行，然后应该在col E中获得所需的数量。
Thx为您的支持！
找到解决方案：
Sub proof（）
'
'proof宏
'
n2 = 26
n1 = 489
对于i = 29 To 489
细胞（i，5）= 0
下一个i
对于i = 1至n2
对于j = 29至n1
a = UCase（sheet2）单元格（i，2））
b = UCase（Sheets（sheet1）。单元格（j，8））
如果a =然后GoTo 10
如果（a<"And b&）和InStr（b，a）然后
'停止
表（sheet1）。单元格（j，5）+表格（sheet2）单元格（i，3）
如果
下一个j
10下一个i
'
End Sub
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!
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
这篇关于搜索哪个单元格包含特定文本并修改偏移单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
