Count Question hElP

brettvba

MrExcel MVP
Joined
Feb 18, 2002
Messages
1,030
Im not to good with counts yet
if i wanted to do a count
with this code

Dim mgs2
mgs2 = CLng(InputBox("Enter Selection To Find", "Hello"))
Cells.Find(What:=(mgs2), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
and then count how many times mgs2 appeared on screen what extra code would i need?

I had this but it only goes once then leaves the for

Dim mgs2
Dim mgstotal
mgs2 = CLng(InputBox("Enter Selection To Find", "Hello"))
Cells.Find(What:=(mgs2), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate

For Each mgs2 In Cells.FindNext(After:=ActiveCell)
mgstotal = msgtotal + 1
Next
This message was edited by brettvba on 2002-04-29 21:43
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Hi Brettvba,

Here's some counting code that doesn't require the use of the Find method:

Sub CountEm()
Dim iCount As Long
Dim Cell As Range
Dim Target As String
Target = InputBox("Enter Selection To Find", "Hello")
If Target = "" Then Exit Sub
Target = "*" & Target & "*"
iCount = 0
For Each Cell In ActiveSheet.UsedRange
If Cell.Value Like Target Then iCount = iCount + 1
Next Cell
MsgBox iCount & " occurrences found", vbInformation, "CountEm Results"
End Sub

This one doesn't require that the value you are searching for be numeric (as yours does), but that could be easily added with an IsNumeric test.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top