Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

asked on

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.
Avatar of Anthony Berenguel
Anthony Berenguel
Flag of United States of America image

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

Avatar of Luis Diaz

ASKER

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.
Avatar of Saurabh Singh Teotia
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...
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?
@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.
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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!!!!!