VBA: Referring to active cells' row in a For/Each loop

January 7, 2022

the aim of my problem is to find a specific value (Text) and then refer to the entire row (or even better only the used range to the right of my active cell) in a For/Each loop.

The first part works fine of finding my value, however, the code for targeting the row of the active cell (so the cell found by the find function), does not work yet:

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

>Solution :

The following code will find the range to the right of your found cell and use your loop to do the comparision for each cell in the range. That part could probably be improved by using 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