| Home |
| Search |
| Today's Posts |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count numbers that start with 1
It works great! I really appreciate the help!
"OssieMac" wrote: 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. -- Regards, OssieMac "andy" wrote: It works great! 1 more question: Do you know how to get those 'count' totals into a cell ,say (D1), on the spreadsheet? Thanks! "OssieMac" wrote: 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 -- Regards, OssieMac |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Number of Days after a Start Date | Excel Discussion (Misc queries) | |||
| Count consecutive cells then start over | Excel Worksheet Functions | |||
| How to count # of dates that fall between a start and end date. | Excel Programming | |||
| Looking to add a leading 0 @ start of numbers | Excel Discussion (Misc queries) | |||
| Can I remove Row Numbers? Or have the Row Numbers start on the Ro | Excel Discussion (Misc queries) | |||
Threaded Mode
