我的問題的目的是找到一個特定的值（文本），然后在 For/Each 回圈中參考整行（或者甚至更好的是僅在我的活動單元格右側使用的范圍）。
第一部分可以很好地找到我的值，但是，用于定位活動單元格行的代碼（因此 find 函式找到的單元格）還不起作用：
Sub Search()
Dim cell As Range
Dim Count As Long
Set cell = Cells.Find(what:="Planned Supply at BP|SL (EA)", LookIn:=xlValues, lookat:=xlWhole)
For Each cell In ActiveCell.EntireRow
If cell.Value = "0" Then
Count = Count 1
End If
Next cell
Range("I1").Value = Count
End Sub
uj5u.com熱心網友回復：
以下代碼將找到找到的單元格右側的范圍，并使用回圈對范圍內的每個單元格進行比較。這部分可能會通過使用來改進WorksheetFunction.CountIf。
Option Explicit
Sub Search()
Dim wks As Worksheet
Set wks = ActiveSheet
Dim cell As Range, sngCell As Range
Dim Count As Long
Set cell = wks.Cells.Find(what:="Planned Supply at BP|SL (EA)", LookIn:=xlValues, lookat:=xlWhole)
If cell Is Nothing Then Exit Sub ' just stop in case no hit
Dim rg As Range, lastColumn As Long
With wks
lastColumn = .Cells(cell.Row, .Columns.Count).End(xlToLeft).Column ' last used column in cell.row
Set rg = Range(cell, .Cells(cell.Row, lastColumn)) ' used rg right from found cell inlcuding found cell
End With
' loop from the original post
For Each sngCell In rg
If sngCell.Value = "0" Then
Count = Count 1
End If
Next
Range("I1").Value = Count
End Sub
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/405365.html
標籤：
下一篇：跳過包含空單元格的行