Count numbers that start with 1

  • Thread starter Thread starter andy
  • Start date Start date
A

andy

Hi,

Is it possible to search a spreadsheet and count the numbers that only start
with the number 1? And then also count how many cells it searched in order
to obtain that value?

For Example:
10
2,100
1,500,100
10,150,280
317

Cells with the number 1 = 3
Total cells looked at = 5

Thanks!
 
Hi Andy,

The following code ignores blank cells. If you don't ignore blank cells then
needs to be modified to ensure only getting the range with data because
otherwise UsedRange is a bit unreliable but ignoring blank cells overcomes
this. Do all of the cells between the first cell top left and the last cell
bottom right of the range to be searched have data? If so, the following code
should return the correct answer anyway.

Sub CountFirstOne()

Dim rngUsed As Range
Dim c As Range
Dim lngOne As Long
Dim lngTot As Long

'Edit "Sheet1" with your sheet name
With Sheets("Sheet1")
For Each c In .UsedRange
'Don't count blank cells
If c.Value <> "" Then
lngTot = lngTot + 1
If Left(c.Value, 1) = "1" Then
lngOne = lngOne + 1
End If
End If
Next c
End With

MsgBox "Cells with the number 1 = " & lngOne _
& vbCrLf & "Total cells looked at = " & lngTot

End Sub
 
It works great! 1 more question: Do you know how to get those 'count'
totals into a cell ,say (D1), on the spreadsheet?

Thanks!
 
Hi Andy,

Insert the following either before the MsgBox, after the MsgBox or in lieu
of the MsgBox.

Range("D1") = lngOne
Range("D2") = lngTot

Or if you want the totals combined in one cell then
Range("D1") = lngOne & ", " & lngTot

Note that when you populate additional cells on the worksheet then the
additional cells get included in the count if you run the code again. If you
don't what this to occur then you need to provide the actual range in which
the count is to be restricted. To do this you need to replace the following 2
lines of the code.

lngUsed = .UsedRange.Cells.Count
For Each c In .UsedRange

with the following

lngUsed = .Range("A3:J200").Cells.Count
For Each c In .Range("A3:J200")

Replace "A3:J200" with your actual range.
 
It works great! I really appreciate the help!

OssieMac said:
Hi Andy,

Insert the following either before the MsgBox, after the MsgBox or in lieu
of the MsgBox.

Range("D1") = lngOne
Range("D2") = lngTot

Or if you want the totals combined in one cell then
Range("D1") = lngOne & ", " & lngTot

Note that when you populate additional cells on the worksheet then the
additional cells get included in the count if you run the code again. If you
don't what this to occur then you need to provide the actual range in which
the count is to be restricted. To do this you need to replace the following 2
lines of the code.

lngUsed = .UsedRange.Cells.Count
For Each c In .UsedRange

with the following

lngUsed = .Range("A3:J200").Cells.Count
For Each c In .Range("A3:J200")

Replace "A3:J200" with your actual range.
Click to expand...
 
Back
Top