Power Query Course in Spanish
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: Count Question hElP

  1. #1
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Damon i can amend that to suit my needs cheers

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •