Excel VBA - 循环变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9704425/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA - variable for loop
提问by CustomX
I can't seem to get my second for loop right. I'm looking for the cell with value 'Persoonlijke prijslijst'. Once I have this cell I need to go up two and delete 8 down. When I debug, it says temp = 0 so I presume it's in my second for loop.
我似乎无法让我的第二个 for 循环正确。我正在寻找值为“Persoonlijke prijslijst”的单元格。一旦我有了这个单元格，我需要向上移动两个并向下删除 8 个。当我调试时，它说 temp = 0 所以我认为它在我的第二个 for 循环中。
Dim i As Integer
For i = 1 To 800
Range("C" & i).Select
If Range("C" & i).Value = "Persoonlijke prijslijst" Then
Dim temp As Integer
For temp = i - 2 To temp + 8
Range("C" & temp).EntireRow.Delete Shift:=xlToLeft
Next temp
End If
Next i
回答by SWa
Another way without looping 800 times:
另一种不循环 800 次的方法：
Sub testing()
Dim rng As Range
Dim fAddress As String
Dim rngRows As Range
With Sheet1.Range("C1:C800")
Set rng = .Find("Persoonlijke prijslijst")
If Not rng Is Nothing Then
fAddress = rng.Address
Do
If rngRows Is Nothing Then
Set rngRows = rows(rng.Row - 2 & ":" & rng.Row + 5)
Else
Set rngRows = Union(rngRows, rows(rng.Row - 2 & ":" & rng.Row + 5))
End If
Set rng = .FindNext(rng)
Loop While Not rng Is Nothing And rng.Address <> fAddress
End If
End With
rngRows.EntireRow.Delete
End Sub
回答by Siddharth Rout
Is this what you are trying?
这是你正在尝试的吗？
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim StrSearch As String
Dim i As Long
'~~> Change this to the relevant sheet name
Set ws = Sheets("Sheet1")
StrSearch = "Persoonlijke prijslijst"
With ws
For i = 800 To 1 Step -1
If .Range("C" & i).Value = StrSearch Then
.Rows(i - 2 & ":" & i + 5).Delete
End If
Next i
End With
End Sub
