Luis Diaz
asked on
VBA Excel : set up a count function
Hello experts,
I am trying to built the following function :
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.
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
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.
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.
Can we integrate lastRowLetter and Range as Parameter instead of defining as static in the function?
Thank you very much for your help.
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?
ASKER
@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.
@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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Working perfectly for me with the following example:
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.
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
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.
ASKER
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(xlCellTyp eConstants )
getcount = Application.WorksheetFunct ion.CountI f(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!!!!!
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(xlCellTyp
getcount = Application.WorksheetFunct
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!!!!!
Open in new window