对于范围内的每个单元格，如果在下一个单元格中找到单独范围内的值，则执行操作 [英] For each cell in a range, if a value in a seperate range is found in the next cell do stuff

问题描述

我构建的这个宏是有效的，但我希望有一个更快的版本或一个可以在更短的时间内完成相同操作的公式.

This Macro I have built works but I am hoping for a faster version or a Formula that will do the same in less time.

我有什么:

    For Each cell In Range("Table_Query_1[[#Data],[Reason2]]")
        For Each PossibleValue In Range("F2", Range("F2").End(xlDown))
            If Len(cell) = 0 Then
             If (InStr(UCase(cell.Offset(0, 1)), UCase(PossibleValue)) <> 0) Then
               cell.Value = PossibleValue.Value
             End If
             Else
                Exit For
             End If
         Next
         If Len(cell) = 0 Then
            cell.Value = cell.Offset(0, -1)
        End If
    Next

我可以使用以下数组公式获得任何工作的唯一另一种方法

The only other way I could get anything to work way with the following Array Formula

=IF(ISNA(MATCH($F$3:$F$10,[@Extra Info],0)),[@Reason],$F$3:$F$10)

但这不适用于第 4 行和第 9 行的部分匹配.我也怀疑这个数组公式会比 vba 宏快得多，而且它还需要更多维护在这种情况下，使用测试值范围 (F2:f3)，因为我必须不断更新该公式，否则我将不得不像 F2:F100 那样制作原始范围，这会导致它花费更长的时间.

but this doesn't work for Partial matches as in the case of Row 4 and 9. I also have my doubts that this array formula would be that much faster then a vba macro along with the fact it would also require more upkeep with the test values range (F2:f3) in this case as I would have to constantly update that formula OR I wouild have to make the original range like F2:F100 witch would cause it to take that much longer.

所以，我想要的是，如果我的值范围内的任何值(在本例中为 F2:F3)，是否在当前行的额外信息列内找到，然后是该行的 Reason2(偏移(0, -1)) 等于匹配的值.但是如果什么都没有找到，那么就使用该行中的 Reason(Offset(0,1)).

So, what i'd like is if ANY value in my range of values (F2:F3 in this case), Is found inside of the Extra Info Column on the current Row , Then Reason2 of that row (Offset(0, -1)) equals the Value that was matched. But if nothing is found then just use the Reason in that row(Offset(0,1)).

第二个问题是，我需要在 QueryTable 刷新后运行宏，但如果我将其设置为单元格上的单元格更改事件，则查询中将发生更改，宏在最终查询表之前运行并完成导入并排序.

And the second Issue is that I need the Macro to Run After the QueryTable refreshes but if I set it as a Cell Change Event on a cell the is in the query that will change, the macro runs and finishes before the Final querytable is imported and sorted.

推荐答案

已解决！

这是我在上面发布的带有初始公式的评论.

This is post the comment that I posted above which had the initial formula.

=IF(COUNT(FIND($F$2:$F$3,C1)),"这里会发生什么",A1)

下面告诉你必须用什么代替What Will Go Here"

The below tells you what has to go in place of "What Will Go Here"

将此公式放在单元格 B2 中.请注意，这是一个数组公式.输入公式后，您必须按 CTRL + SHIFT + ENTER.

Put this formula in cell B2. Note that this is an Array Formula. You will have to press CTRL + SHIFT + ENTER after you enter the formula.

=IF(COUNT(FIND($F$2:$F$4,C2)),INDEX($F$2:$F$4,MATCH(SUM(IF(ISNUMBER(--FIND($F$2):$F$4,C2,1)),--FIND($F$2:$F$4,C2,1))),FIND($F$2:$F$4,C2,1),0),0),A2)

截图

说明:

FIND($F$2:$F$4,C2,1) 与数组一起使用时返回一个数组.要检查值，您可以突出显示它并按 F9，它会告诉您找到匹配项的位置.看这个截图

FIND($F$2:$F$4,C2,1) when used with an array returns an array. To check the values you can highlight it and press F9 and it will tell you the position at which the match is found. See this screenshot

所以它告诉我们它在 4532 的第 3 个位置找到了匹配.但它没有告诉我们它找到了匹配的内容.

So it tells us that it found the match at the 3rd position in 4532. It yet doesn't tell us with what did it find a match.

现在下一步是从数组中检索该数字的位置.因此，在上面的示例中，它将是位置 2，为了找到该位置，我们将使用 MATCH() 并且要使用 MATCH，我们将需要 3

Now the next step is to retrieve the position of that number from the array. So in the above example it will be position 2 and to find that position we will use MATCH() and to use MATCH we will need that 3

所以为了从数组中检索3，我们使用这个公式

So to retrieve 3 from the array we use this formula

=SUM(IF(ISNUMBER(--FIND($F$2:$F$4,C2,1)),--FIND($F$2:$F$4,C2,1)))

现在我们有了 3，所以我们将在 Match 中使用它来查找 Possible Value

Now we have that 3 so we will use it in Match to find the position in the Possible Value

=MATCH(SUM(IF(ISNUMBER(--FIND($F$2:$F$4,C2,1)),--FIND($F$2:$F$4,C2,1))),FIND($F$2:$F$4,C2,1),0)

这会给我们 2

现在我们知道数字在Possible Value中的位置.要找到该数字，我们将使用 INDEX

Now we know the position of the number in the Possible Value. To find that number we will use INDEX

=INDEX($F$2:$F$4,MATCH(SUM(IF(ISNUMBER(--FIND($F$2:$F$4,C2,1)),--FIND($F$2:$F$4,C2,1))),FIND($F$2:$F$4,C2,1),0),0)

示例工作簿

http://wikisend.com/download/280280/Sample.xlsx

这篇关于对于范围内的每个单元格，如果在下一个单元格中找到单独范围内的值，则执行操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！