On Sunday, October 26, 2014 8:17:57 PM UTC-7, GS wrote:
One way...

Sub FindMyVal()
Dim n&, k&, sz$

ReDim vArray(1 To WorksheetFunction.CountIf(ActiveSheet.UsedRange,
5))
For n = LBound(vArray) To UBound(vArray)
With ActiveSheet.UsedRange
For k = 1 To .Cells.Count
If .Cells(k) = 5 And InStr(sz, .Cells(k).Address) = 0 Then
sz = sz & "," & .Cells(k).Address: Exit For
End If
Next 'k
End With 'ActiveSheet.UsedRange
Next 'n
' sz = Replace(Mid(sz, 2), ",", ", ")
Range(Replace(Mid(sz, 2), ",", ", ")).Select
End Sub

--
Garry

Thanks, Garry.

I thought this to be an easier task until I flailed about with hapless attempts.

The only thing I got correct in my mind as to how to do this was to read the desired cells (containing 5) into an array.

With your code:

If the UsedRange has 8 cells with the number 5 in them, we have an 8 element vArray.

Then for each cell in the UsedRange, the If statement must = 5 and return 0 from the InStr query for the cell address to be remembered in sz.

If .Cells(k) = 5 And InStr(sz, .Cells(k).Address) = 0 Then
sz = sz & "," & .Cells(k).Address: Exit For
End If

I don't get the InStr portion and sz.

But it sure does work.

Howard