我正在尝试读取 G 列中的所有值，直到找到一个空白单元格。如果值为“Permits Received”或“Cancelled”，那么我会在 H 列中写上“Ready to Build”。如果我遇到的不是收到或取消的任何内容，那么我会写上“Missing Permits”。所以，我需要读取 G 列中所有填充的单元格并在 H 列中写入准备好...或丢失...。我的代码的问题是 1）它可能不是最好的方法，2）它只读取第一个G列中的单元格然后写入输出。
这是一个自动化的工作簿，除了这个循环之外，它就像一个冠军。我一直在玩 For Next、Do While 和 For Each 并取得不同的成功，但下面的代码是我最接近的。
Dim i As Integer, j As Integer, rng As Range
Set rng = Range("$G$2:$G$" & ActiveSheet.UsedRange.Rows.Count) ' Set range to all used rows in column G
For i = 2 To rng.Rows.Count
Do While Cells(i, 7).Value = ""
If Cells(i + 1, 7).Value = "Permits Received" Or Cells(i + 1, 7).Value = "Cancelled" Then
Cells(i, 8).Value = "Ready to Build"
Else: Cells(i, 8).Value = "Missing Permits"
End If
i = i + 1
If i = rng.Rows.Count Then Exit For ' Without this code it will read all rows, not just used rows
Loop
Next i
我希望循环读取所有 G 列值，然后决定它是“准备构建”还是“缺少许可证”。如果不包括 Exit For，代码运行到 35766 然后溢出错误。
（新）每行或每组行（col G）我只需要一个输出行（col H）。附加的图像显示了输出的外观。非常感谢，看了这么多！！！我已经盯着它一个星期了！
基于发布的图像的解决方案：
假设任何FIB:BUR组的默认值是"Missing Permits "，除非它的所有FIB:PERMITs的值都是" Permits Received"或"Cancelled" ，否则它应该被标记为"Ready to Build"。
此建议的解决方案使用AutoFilter 对象 (Excel)与Range.SpecialCells 方法 (Excel)相结合。创建一个范围，其中的组由Range.Areas 属性 (Excel)分隔。然后它使用For…Next和WorksheetFunction.CountIf来验证是否存在" Permits Received"或"Cancelled"，以及Range.Offset 属性 (Excel)来设置组的结果值。
Sub Solution()
Dim rSrc As Range, rTrg As Range
Dim rArea As Range
Dim bCnt As Byte 'Change data type to long if the number of FIB:PERMITs by FIB:BUR exceeds 255
With ThisWorkbook.Worksheets("DATA") 'change as required
If Not (.AutoFilter Is Nothing) Then .Cells(1).AutoFilter
Set rSrc = .Cells(2, 7).Resize(-1 + .UsedRange.Rows.Count, 2)
End With
With rSrc
.Columns(2).ClearContents
.Offset(-1, 0).Resize(1 + .Rows.Count).AutoFilter
.AutoFilter Field:=1, Criteria1:="<>"
Set rTrg = .Columns(1).SpecialCells(xlCellTypeVisible)
.AutoFilter
End With
For Each rArea In rTrg.Areas
bCnt = 0
With WorksheetFunction
bCnt = .CountIf(rArea, "Cancelled")
bCnt = bCnt + .CountIf(rArea, "Permits Received")
rArea.Cells(1).Offset(-1, 1).Value2 = _
IIf(bCnt = rArea.Rows.Count, "Ready to Build", "Missing Permits")
End With: Next
End Sub
回答原始问题
而不是使用的Do…Loop范围内的For…Next，你也可以使用IF…ELSEIF或选择Case语句。这个提议的解决方案使用Select Case
Sub Solution_1()
Dim rTrg As Range, lRow As Long
With ThisWorkbook.Worksheets("DATA") 'change as required
Set rTrg = .Cells(2, 7).Resize(-1 + .UsedRange.Rows.Count, 2)
End With
With rTrg
For lRow = 1 To .Rows.Count
Select Case .Cells(lRow, 1).Value2
Case vbNullString 'NO ACTION!
Case "Permits Received", "Cancelled"
.Cells(lRow, 2).Value2 = "Ready to Build"
Case Else
.Cells(lRow, 2).Value2 = "Missing Permits"
End Select: Next: End With
End Sub
但是，我尽量避免For…Next尽可能避免，因此此替代解决方案使用AutoFilter 对象 (Excel)与Range.SpecialCells 方法 (Excel)相结合。
Sub Solution_2()
Dim rTrg As Range
With ThisWorkbook.Worksheets("DATA") 'change as required
If Not (.AutoFilter Is Nothing) Then .Cells(1).AutoFilter
Set rTrg = .Cells(2, 7).Resize(-1 + .UsedRange.Rows.Count, 2) ' Set range to all used rows in column G
End With
With rTrg
.Offset(-1, 0).Resize(1 + .Rows.Count).AutoFilter
.Columns(2).Value2 = "!"
.AutoFilter Field:=2, Criteria1:="!"
.AutoFilter Field:=1, Criteria1:="=Cancelled", _
Operator:=xlOr, Criteria2:="=Permits Received"
.Columns(2).SpecialCells(xlCellTypeVisible).Value2 = "Ready to Build"
.AutoFilter Field:=1, Criteria1:="<>"
.Columns(2).SpecialCells(xlCellTypeVisible).Value2 = "Missing Permits"
.AutoFilter Field:=1
.Columns(2).SpecialCells(xlCellTypeVisible).ClearContents
.Cells(1).AutoFilter
End With
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句