excel - 如何在 VBA 中使用 .Find() 函数计算范围

Question

我有以下 Excel 工作表

通过以下.Find()函数，我找到了我需要的值和单元格。

Dim finalResult as Range
Dim finalValue as String
Dim headValue as string

finalValue = "Banana"
headValue = "Food"

Set finalResult = Worksheets("mySheet").Range("A1:A20").Find(finalValue, LookIn:=xlValues, After:=headValue)

我的问题是：知道范围的开始 = 食物和结束 = 香蕉，我如何计算之间有多少个单元格？

谢谢

标签： excelvbacountrange

Answer 1

请使用此代码。有更多方法可以找到你的细胞，但我会使用你喜欢的一种：

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