VBA - Find Method with Multiple Returns

Hello,

Without looking at your file, the Split function allows you to extract the row or column from an absolute address:
Split("$B$12", "$")(1) returns the column (B)
Split("$B$12", "$")(2) returns the row (12)

--
Best regards
Patrice

Hello, I think this gives you the line number:

Range(Tb(i)).Row

Thank you all,
Patrice's answers (first response) and yg_be's response are exactly what I was looking for
Patrice's second response does not work; it returns the same answer as many times as there are occurrences, and moreover, the answer is wrong.
Indeed, Tb(i) takes the sought value.

Hello Michel and thank you for your tutorials.
I am looking to retrieve, for each occurrence found, the data from column "X" corresponding to the found row.
That's why the answers from Patrice and Yg_be perfectly satisfied me.
Here is the complete code:

Sub RechMulti()
Dim R As Long, TB()
Dim i As Integer, Rch As String
Rch = 22 'for example
 Sheets("Feuil1").Columns(7).ClearContents 'Column(7) for the results
 R = RechFind(Rch, ThisWorkbook.Name, "Feuil1", "B1:E500", TB())
 If R > 0 Then
 For i = 0 To R - 1 ' or ubound(TB)
 Sheets("Feuil1").Range("G1") = "Occurrences found for: " & Rch
 Sheets("Feuil1").Cells(i + 2, 7) = Cells(Range(TB(i)).Row, 2)
 Next i
 Else
 MsgBox "Not found", , "Search"
 End If
End Sub

However, I do not know how to search for an exact value, because in this example, it gives me 122, 422, 220... I tried the "", but it doesn't change anything.

Finally, to align with Patrice's (2nd proposal), it was necessary to replace
TBadress(Ix) = Cherche.Address
with
TBadress(Ix) = Cherche.Row
I understood your suggestions, I just need to find a way to search for "exactly".

Hi,

Otherwise, you can modify the module to return the value instead of the address, just replace:

TBadress(Ix) = Cherche.Address

with

TBadress(Ix) = Cherche.Value 

--
Best regards
Patrice

Hello,

I don't quite understand what you're trying to do since you want the value and not the address.
If in the mentioned demo you're looking for all the values 12, the answer will be 12!
Unless 12 is included within other characters, for example "azerty12uiop".

It would be best if you attached your workbook.

Upload the workbook without confidential data as an attachment on "mon-partage.fr"
and right-click to paste the shortcut in your message.

And explain what you want to achieve.

Michel

searchfind is an awful and complicated mess

since Mouftie wants the lines in column G where the searched reference is

Option Explicit
Const Ref As Integer = 12 'for example
'------------
Sub search_ref()
Dim Zone As Range, Cell As Range
Dim Count As Integer
Dim Total_out, Counter As Integer
'Dim test
 Application.ScreenUpdating = False
 Columns("G").Clear
 Set Zone = Range("B1:E500")
 
 Count = Application.CountIf(Zone, Ref)
 If Count = 0 Then GoTo empty
 With Zone
 Set Cell = Zone.Find(what:=Ref, LookIn:=xlValues, lookat:=xlWhole)
 For Counter = 1 To Count
 'test = Cell.Address
 Cells(Counter, "G") = Cell.Row
 Set Cell = .FindNext(Cell)
 Next
 End With
 'sort in ascending order if cell B1 = Ref (otherwise row 1 is at the bottom)
 If Range("B1") = Ref Then
 Range("G1:G" & Count + 1).Sort Key1:=Range("G1"), Order1:=xlAscending
 End If
 Exit Sub
'error handler
empty:
 MsgBox Ref & " unknown in the area"
End Sub

and the model:
https://mon-partage.fr/f/QCeLgCgv/

--
Michel