Jest to alternatywa do pętli. Często z racji formatu danych pętla nie znajduje rekordu w arkuszu, choć poszukiwany rekord faktycznie jest w zasobie.
' https://stackoverflow.com/questions/32190029/excel-vba-find-row-number-of-matching-value
Dim FoundCell As Range
Dim MyValue As String
' ------------------------------------------------------------
Public Sub FindYourValue()
Dim SearchingResult
MyValue = "YourStringValue"
SearchingResult = SearchThisValue()
If SearchingResult <> 0 Then
' Replace following Debug.Print with your code
Debug.Print "Your value is placed on " & SearchingResult & " row."
Else
' Replace following Debug.Print with your code
Debug.Print "Your value has no been found."
End If
End Sub
' ------------------------------------------------------------
Private Function SearchThisValue()
Dim FirstRowNo, LastRowNo, ColNo As Long
FirstRowNo = 1
LastRowNo = 3
ColNo = 1
With ThisWorkbook.Sheets("SheetName")
Set FoundCell = Range(.Cells(FirstRowNo, ColNo), .Cells(LastRowNo, ColNo)).Find(What:=MyValue)
If Not FoundCell Is Nothing Then
SearchThisValue = FoundCell.Row
End If
End With
End Function
Jest jeszcze jeden sposób na wyszukanie unikalnej wartości w arkuszu:
Source: https://stackoverflow.com/questions/19504858/find-all-matches-in-workbook-using-excel-vba
Public TFSdataRowIndex, TFSdataColumnIndex As LongPrivate Sub CheckWhereIsTableFromTfs()
Dim WholeActiveSheet As Range
With ActiveSheet.UsedRange
Set WholeSheet = .Cells.Find(What:="UniqueValue")
If Not WholeSheet Is Nothing Then
TFSdataRowIndex = WholeSheet.Row
TFSdataColumnIndex = WholeSheet.Column
End If
Set WholeSheet = Nothing
End With
End Sub