Как считать в диапазоне с помощью функции .Find () в VBA

Используйте этот код, пожалуйста. Есть и другие способы найти ваши клетки, но я буду использовать тот, который вам больше нравится:

Sub testFindRange()
 Dim finalResFood As Range, finalResBanana As Range
 Dim finalValue As String, headValue As String, sh As Worksheet
 Set sh = Worksheets("mySheet")' Having more workbooks open, it would be good to define it using the workbook where it belongs, too.

 finalValue = "Banana"
 headValue = "Food"

 Set finalResBanana = sh.Range("A1:A20").Find(finalValue, LookIn:=xlValues)
 Set finalResFood = sh.Range("A1:A20").Find(headValue, LookIn:=xlValues)
 Debug.Print finalResBanana.row - finalResFood.row
End Sub

На самом деле несколько вариантов:

Sub Test()

Dim rng As Range: Set rng = Worksheets("mySheet").Range("A:A")
Dim rng1 As Range, rng2 As Range

'-------------------------------------
'Option 1: Range.Object
Set rng1 = rng.Find("Food", Lookat:=xlWhole)
Set rng2 = rng.Find("Banana", Lookat:=xlWhole)

If Not rng1 Is Nothing And Not rng2 Is Nothing Then
    Debug.Print rng2.Row - rng1.Row
End If

'-------------------------------------
'Option 2: Application.Match
With Application
    rw1 = .Match("Food", rng, 0)
    rw2 = .Match("Banana", rng, 0)
    If IsError(rw1) = False And IsError(rw2) = False Then
        Debug.Print rw2 - rw1
    End If
End With

'-------------------------------------
'Option 3: xlDown
Set rng1 = rng.Find("Food", Lookat:=xlWhole)
If Not rng1 Is Nothing Then
    Debug.Print rng1(rng1.Rows.Count, 1).End(xlDown).Row - rng1.Row
End If

End Sub