List of Cell References?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 13 Jul 2006 10:30 GMT

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.

Reply to this Message

Bob Phillips - 13 Jul 2006 11:51 GMT

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

Signature

HTH

Bob Phillips

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

> Hi,
> I have a spreadsheet where column 'A' is a long list of values which is
[quoted text clipped - 8 lines]
>
> Paul Evans.

Reply to this Message