Excel - VBA：只要单元格的内容等于特定值就循环

Excel - VBA : loop as long as content of a cell equals specific value

提问by Phalanx

I am trying to modify my code to allow more factors to be taken into account when running a loop. Here is what I have so far, it's a loop running for i = 2 to 605 (because between 2 and 605, my G column's valueis always the same "Makati City").

For this loop, I have some actions defined and it's working well. Here is the relevant code showing what my loop is doing :

我正在尝试修改我的代码以允许在运行循环时考虑更多因素。这是我到目前为止所拥有的，它是一个循环，运行 i = 2 到 605（因为在 2 到 605 之间，我的G 列的值始终是相同的“马卡蒂市”）。

对于这个循环，我定义了一些操作并且运行良好。这是显示我的循环正在做什么的相关代码：

For i = 2 To lRowBldg
Range("B" & i).Activate

'try to find a match btwn active cell and one of the elements from parsed address
    For Each cell In elementsListRange.Cells
      If Match(ActiveCell.Value, cell.Value) Then
        Range("K" & i).Value = Range("K" & i).Value + 13
        Else
        Range("K" & i).Value = Range("K" & i).Value + 0
      End If
      If Match(ActiveCell.Offset(0, 4).Value, cell.Value) Then
      Range("K" & i).Value = Range("K" & i).Value + 8
        Else
        Range("K" & i).Value = Range("K" & i).Value + 0
      End If
    Next

Next i

But as I plan to make some modifications to this file, I need to rethink my code. So far, I had only this file for one city so basically I could loop from the first to the last value and it was ok. Now, I plan to add more cities : for example, from 2 to 605 (Makati City), from 606 to 900 (blabla City), from 901 to ... and so on.

但是当我打算对这个文件做一些修改时，我需要重新考虑我的代码。到目前为止，我只有一个城市的这个文件，所以基本上我可以从第一个值循环到最后一个值，这没问题。现在，我计划添加更多城市：例如，从 2 到 605（马卡蒂市），从 606 到 900（布拉布拉市），从 901 到...等等。

What I try to do is something like this :
"Loop for as long as the value of the cell in G column is equal to XXXXX (could be Makati City, could be blabla City, whatever)"
And if I mention Makati City, it will loop for i = 2 to 605, if it's blabla City, then it will loop for i = 606 to 900, and so on.

我尝试做的是这样的事情：
“只要 G 列中单元格的值等于 XXXXX（可能是马卡蒂市，也可能是布拉布拉市，等等），就循环
下去”如果我提到马卡蒂市，它将循环 i = 2 到 605，如果它是 blabla City，那么它将循环 i = 606 到 900，依此类推。

Do you have any idea about how to do this in such a way that wouldn't be too resource consuming as my file could end up being very long ?

您是否知道如何以一种不会占用太多资源的方式执行此操作，因为我的文件最终可能会很长？

Thanks so much in advance !

非常感谢！

回答by Christian Sauer

Use a while? Here is some Idea....

使用一段时间？这是一些想法....

    Sub Test()
        dim rng as range
        rng = worksheet.find

        While rng.Value2 LIKE "Makhati City"
        'your logic here
        rng.offset(row+1,col+0 or like this)
        Wend
    End Sub

回答by Our Man in Bananas

you could try this, using columns L & N, and avoiding a loop entirely:

你可以试试这个，使用列 L & N，并完全避免循环：

put all the code in a standard module then modify the code in setColumnKValuesto search for differenct cioy names in column G.

将所有代码放在一个标准模块中，然后修改setColumnKValues 中的代码以在 G 列中搜索不同的 cioy 名称。

ASSUMPTIONS:

假设：

• That the differencet cities will be grouped together
• that you can use a couple of extra columns (in this case L & N) as intermediates
• that you will call setFormulasColumnK in the order of the cities on the sheet
• that, based on the code in your question, you want to add a number to column K if the condition is met.

• 不同的城市将被组合在一起
• 您可以使用几个额外的列（在本例中为 L 和 N）作为中间体
• 您将按照工作表上城市的顺序调用 setFormulasColumnK
• 根据您问题中的代码，如果满足条件，您希望向 K 列添加一个数字。

How does it work:first, in column N we put marker values showing where the city changes then, in column L we put in a formula, if it matches the city passed in, then L=K+13 finally, we copy paste the new values in column K, and clear columns L & N

它是如何工作的：首先，在 N 列中我们放置标记值显示城市的变化，然后在 L 列中我们放入一个公式，如果它匹配传入的城市，那么 L=K+13 最后，我们复制粘贴K 列中的新值，并清除 L 和 N 列

Private oLastRange As Range
Private iFirstCell As Integer
Private iLastCell As Integer
Private lLastRow As Long

Sub setFormulasColumnK(ByVal sCity As String)

Dim sFormula As String
Dim oRange As Range

lLastRow = Cells(Rows.Count, Range("G1").Column).End(xlUp).Row

Range("N4:N" & lLastRow).Formula = "=IF(G4<>G5,NA(),"""")"

If Not Range("G:G").Find(sCity) Is Nothing Then
    iFirstCell = Range("G:G").Find(sCity).Row
Else
    Exit Sub
End If

Set oRange = Range("N" & iFirstCell)

iLastCell = Range("N" & iFirstCell & ":N" & lLastRow).Find("#N/A", oRange).Row

Range("L" & iFirstCell & ":L" & iLastCell).Formula = "=IF(TRIM(G:G)=""" & sCity & """,K:K+13,0)"
Set oLastRange = Range("L" & iLastCell)

End Sub

Sub setColumnKValues()

    Set oLastRange = Nothing

    Call setFormulasColumnK("Makati City")
    'MsgBox oLastRange.Address
    Call setFormulasColumnK("London")
    'MsgBox oLastRange.Address
    Call setFormulasColumnK("Birmingham")
    'MsgBox oLastRange.Address
    Call setFormulasColumnK("Moscow")
    'MsgBox oLastRange.Address
    Call setFormulasColumnK("Luxembourg")
    'MsgBox oLastRange.Address
    Call setFormulasColumnK("Paris")
    'MsgBox oLastRange.Address

    Range("L4" & ":L" & lLastRow).Copy
    Range("K4" & ":K" & lLastRow).PasteSpecial xlPasteValues
    Range("N4:N" & lLastRow).Clear
    Range("L4" & ":L" & lLastRow).Clear

End Sub