VBA Excel : set up a count function

Hello experts,

I am trying to built the following function :

Sub RunCount()

Var = count("Item NOT FOUND in sheet: SAP", Range("B2:B1000"), "SAP-CCWT")

MsgBox ("The number of Non items found is equal to " & Var)
End Sub


Function count(find As String, lookin As Range, SheetName As String) As Long
   
   Dim cell As Range
   
   'Apply the for each loop for an specific sheet without making a select of the sheet
   'Define a LastUsedRangeRow for the lookin variable instead of  defining a specific Range

   For Each cell In lookin
       If (cell.Value = find) Then count = count + 1 '//case sens
   Next
    
End Function

Open in new window



What I want to do is to:

-apply the loop for an specific worksheet without selecting it. Thought a launch the vba from a different sheet.
-integrate a last row for the reported lookin range. In my example I put an specific Range("B2;B1000").
I would like to call the function by just reporting the string "B2: B" or just "B" and the function should take into account first and last row of the reported range.

Thank you very much for your help.
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony BerenguelCommented:
this should help get you started. Let me know if you have questions.
Sub RunCount()

    Var = count("Item NOT FOUND in sheet: SAP", Range("B2:B1000"), "SAP-CCWT")
    
    MsgBox ("The number of Non items found is equal to " & Var)
End Sub


Function count(find As String, lookin As Range, SheetName As String) As Long
   
    Dim cell As Range
    Dim thisSheet As Worksheet
    'Dim lookin As Range
    Dim lastRow As Long
    
    
    Set thisSheet = Sheets(SheetName)
    
    'Apply the for each loop for an specific sheet without making a select of the sheet
    'Define a LastUsedRangeRow for the lookin variable instead of  defining a specific Range
    With thisSheet
            
        lastRow = .Range("b2").End(xlDown).Row
        Set lookin = .Range("b2:b" & lastRow)
        
        For Each cell In lookin
            If (cell.Value = find) Then count = count + 1 '//case sens
        Next
    End With
    
End Function

Open in new window

0
LD16Author Commented:
Thank you, however the Range are not defined as parameter.

Can we integrate lastRowLetter and Range as Parameter instead of defining as static in the function?

Thank you very much for your help.
0
Saurabh Singh TeotiaCommented:
Are you sure you want to do this with loop as you can do this without loop for sure which will be time consuming and let me know if you are interested i can write a code for you accordingly...
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Anthony BerenguelCommented:
Are you asking to set up the column in the range as a parameter?  For example if you want to check column D you would send "D" as a parameter?
0
LD16Author Commented:
@Athony: Yes exactly.

@Saurabh Singh: If you have a faster way to perform a count through a function in which you include the name of the sheet
The String to search.
The Range concerned by the count I am in.

The most important is that it should be done through a function that I can call it with dynamically based on my parameters listed above.
0
Saurabh Singh TeotiaCommented:
You can use this...

Function getcount(str As String, cl As String, sh As String)

    Dim ws As Worksheet

    Set ws = Sheets(sh)

    Dim rng As Range

    Set r = ws.Range(cl & ":" & cl).SpecialCells(xlCellTypeConstants)

    getcount = Application.WorksheetFunction.CountIf(r, str)


End Function

Open in new window


Workbook for reference...

Saurabh...
Chckcnt.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Working perfectly for me with the following example:

Function getcount(str As String, cl As String, sh As String)

    Dim ws As Worksheet

    Set ws = Sheets(sh)

    Dim rng As Range

    Set r = ws.Range(cl & ":" & cl).SpecialCells(xlCellTypeConstants)

    getcount = Application.WorksheetFunction.CountIf(r, str)


End Function

Sub RunGetCount()
Var = getcount("RERE", "A", "Sheet1")
MsgBox ("The count is equal to " & Var)
End Sub

Open in new window


One remark if I want to use the like "*" "*" of a string example I have a value in column A as follows "RERE toto" I want to take into account in the count this value as it contains "RERE"

How should I apply adapt the countif function?

Thank you very much for your help.
0
LD16Author Commented:
I was able to find the solution just :



Function getcount(str As String, cl As String, sh As String)

    Dim ws As Worksheet

    Set ws = Sheets(sh)

    Dim rng As Range

    Set r = ws.Range(cl & ":" & cl).SpecialCells(xlCellTypeConstants)

    getcount = Application.WorksheetFunction.CountIf(r, "*" & str & "*")


End Function

Sub RunGetCount()
Var = getcount("RERE", "A", "Sheet1")
MsgBox ("The count is equal to " & Var)
End Sub


Thank you very much for your help!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.