vba 获取 Excel 行索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10087667/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get Excel row index
提问by user1323888
I have an Excel spreadsheet with currency pairs USD/XYZor XYZ/USD, etc. in one column. I want to know the row number of the cell USD/ABC(suppose). Also, I want to know which rows have USDas the first three characters and which don't.
我有一列包含货币对USD/XYZ或XYZ/USD等的 Excel 电子表格。我想知道单元格的行号USD/ABC（假设）。另外，我想知道哪些行有USD前三个字符，哪些没有。
I want to do this using VBA.
我想使用 VBA 来做到这一点。
回答by assylias
Assuming the currency pairs are in column A, you can use a formula:
假设货币对在 A 列中，您可以使用一个公式：
=MATCH("USD/EUR",A:A,0)
It will return the row where the currency is located (if there are duplicates, the row where it first appears is returned).
它将返回货币所在的行（如果有重复，则返回它首先出现的行）。
If you want to use VBA, you can read the data in an array and loop over the array (below an example looking for "EUR/USD" which you can adapt to your needs):
如果您想使用 VBA，您可以读取数组中的数据并循环遍历该数组（下面是一个寻找“EUR/USD”的示例，您可以根据自己的需要进行调整）：
Sub test()
Dim row As Long
row = findCurrencyPair("EUR/USD")
If row = 0 Then
MsgBox "EUR/USD not found"
Else
MsgBox "EUR/USD found in row " & row
End If
End Sub
Function findCurrencyPair(pair As String) As Long
Dim data As Variant
Dim i As Long
Dim lastRow As Long
With Sheets("SpotRates")
lastRow = .Cells.Find(What:="*", after:=.Range("A1"), LookIn:=xlFormulas, _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).EntireRow.row
data = .Range("A1:A" & lastRow) 'Replace A with the relevant column name
End With
If IsArray(data) = False Then Exit Function 'Empty sheet
For i = LBound(data, 1) To UBound(data, 1)
If data(i, 1) = pair Then
findCurrencyPair = i
Exit Function
End If
Next i
'if not found, returns 0
End Function
EDIT
编辑
Following @Readify comment, an even simpler solution would be (assuming the data only appears in one column):
在@Readify 评论之后，一个更简单的解决方案是（假设数据只出现在一列中）：
Function findCurrencyPair(pair As String) As Long
On Error Resume Next
findCurrencyPair = Sheets("SpotRates").Cells.Find(What:=pair).Row
End Function
