List of Cell References?

P

Paul

Hi,
I have a spreadsheet where column 'A' is a long list of values which is
either a blank cell or the word 'FOUND'.

Is there a way of displaying in one cell up the top, for example B1 which
can contain all the cell references where the word 'FOUND' is listed, e.g.
the content of B1 is a string "A1,A4,A10,A20,A22" based on the cells
1,4,10,20,22 in column 'A' contains the word 'FOUND'. ??

Thanks

Paul Evans.
 
B

Bob Phillips

Sub ListFound()
Dim cell As Range
Dim sFirst As String

Range("B1").Value = ""
With Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Set cell = .Find("FOUND", LookIn:=xlValues)
If Not cell Is Nothing Then
Range("B1").Value = cell.Address(False, False) & ", "
sFirst = cell.Address
Do
Set cell = .FindNext(cell)
If Not cell Is Nothing Then
If cell.Address <> sFirst Then
Range("B1").Value = Range("B1").Value & _
cell.Address(False, False) & ", "
End If
End If
Loop While Not cell Is Nothing And cell.Address <> sFirst
End If
End With
Range("B1").Value = Left(Range("B1").Value, Len(Range("B1").Value) - 2)

End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top