Return Cell Reference of multiple conditions

[Hastings]

I would like to execute a script (macro) that would search a spreadsheet and return the cell reference if:

Cell value = Home or Buy or Rent

So my array results would be for example:
A16
Z22
R100
B210

Would be even better if besides the cell references, I could see the value of the cell also. For example:
A16 Home
Z22 Buy
R100 Rent

 

[Peter_SSs]

See if something like this would help.

My original sample data is in A1:J20 and the code below produced the list in columns M:N

Hastings.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Other			Flat				Flat	Flat
2		Home			Flat		Other						B2	Home
3	Flat		Sell		Buy					Home			J3	Home
4	Flat		Sell						Sell				E11	Home
5	Other	Sell											B12	Home
6		RENT			Sell								C18	Home
7									Other				E19	Home
8	Buy												E3	Buy
9					Flat								A8	Buy
10	Other												J16	Buy
11	Sell				Home	Rent							A19	Buy
12		Home							Other	Other			C20	Buy
13													B6	RENT
14													F11	Rent
15	Flat	Sell							Sell				F20	rent
16										Buy
17		Sell						Flat
18		Flat	Home							Sell
19	Buy				Home			Flat
20			Buy			rent				Sell
Sheet1

Sub Find_Values()
  Dim Itm As Variant
  Dim FirstAddr As String
  Dim rFound As Range
  Dim nr As Long
  
  Const myVals As String = "Home|Buy|Rent"
  
  nr = 2
  With Range("A1:J20")
    For Each Itm In Split(myVals, "|")
      Set rFound = .Find(What:=Itm, LookAt:=xlWhole, MatchCase:=False)
      If Not rFound Is Nothing Then
        FirstAddr = rFound.Address
        Do
          Range("M" & nr).Resize(, 2).Value = Array(rFound.Address(0, 0), rFound.Value)
          nr = nr + 1
          Set rFound = .Find(What:=Itm, After:=rFound, LookAt:=xlWhole, MatchCase:=False)
        Loop Until rFound.Address = FirstAddr
      End If
    Next Itm
  End With
End Sub

 

[Hastings]

Yes this is excellent thank you for your help!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.