a count function
 

I would like to code this :
within a range of cells, containing numbers, I would like to find out :
* how many
* at which location
a given number is located.

Example in which cell(s) is number "56" stored for the following cell values
: 12 456 78541 56 1562

--- 3 times : in cell 2, 4 and 5.

Thanks for the code

a count function
 

Select you range and run:

Sub Mfindit()
Dim r As Range
Dim i As Long
Dim V As Integer
i = 0
V = InputBox("search value")
For Each r In Selection
If r.Value = V Then
MsgBox "found number in column " & r.Column & " row " & r.Row
i = i + 1
End If
Next
MsgBox "total found= " & i
End Sub

--
Gary''s Student


"M" wrote:

I would like to code this :
within a range of cells, containing numbers, I would like to find out :
* how many
* at which location
a given number is located.

Example in which cell(s) is number "56" stored for the following cell values
: 12 456 78541 56 1562

--- 3 times : in cell 2, 4 and 5.

Thanks for the code

a count function
 

The following works and may be of some use. I have included comments which
should make it understandable

Option Explicit

Sub FindHiddenNumber()
Dim rBlock As Range
Dim c As Range
Dim sTarget As String
Dim sMessage As String
Dim iCount As Integer

'Set the target to search for
sTarget = "56"

'And the range to be searched
Set rBlock = Range("A1:A5")

'Now step through all the cells in the range and look for the target figures
For Each c In rBlock
If InStr(1, CStr(c.Value), sTarget) < 0 Then 'A match is found
sMessage = sMessage & c.Address(False, False) & vbCrLf 'Add it
to result
iCount = iCount + 1 'Count the
occurences
End If
Next

'Prepare the message box with the results
If sMessage = "" Then 'No occurence found
sMessage = "No occurences found"
MsgBox sMessage 'Advise nil result
Else
MsgBox sTarget & " was found " & iCount & " times in the following
cells:" & vbCrLf & _
sMessage 'Message will contain list of cells with target
figures
End If

End Sub


"M" wrote:

I would like to code this :
within a range of cells, containing numbers, I would like to find out :
* how many
* at which location
a given number is located.

Example in which cell(s) is number "56" stored for the following cell values
: 12 456 78541 56 1562

--- 3 times : in cell 2, 4 and 5.

Thanks for the code