Macro to count entries

Microsoft Excel 2003 (full product)
September 21, 2009 at 01:11:14
Specs: Windows XP, PENTIMUM -4/1GB
Hi,
every day to create status entry i have to search for two condition for example in column A if team is "RS" and in column B status is "pending" then count 1.
i have to count for all the entries in the table.

please help me to create macro which automatically search for the value and give me the count.

thanks in advance for your help.

Regards,
Dharmesh


See More: Macro to count entries

Report •


#1
September 21, 2009 at 05:21:21
Here is a very quick attempt, this should do it, obviously make some changes to suite your needs, this uses Column A for "RS" and column D for "Pending" to change it to refer to column B change the offset to 1

Private Sub CommandButton1_Click()

Dim Urange, uRange1
Dim LRange, lRange1
Dim BCell, BCell1 As Range
Dim TestVal As String
Dim nVersion As String
Dim nCount As Integer

Set Urange = Range("A1")
Set LRange = Range("A" & GetLastA)

Set uRange1 = Sheet3.Range("A1")
Set lRange1 = Sheet3.Range("A" & GetLastA)


For Each BCell In Range(Urange, LRange)
TestVal = BCell.Value
nVersion = BCell.Offset(0, 3).Value


If TestVal = "RS" And nVersion = "Pending" Then

nCount = nCount + 1

End If
Next BCell

MsgBox nCount

End Sub

Private Function GetLastA() As Integer
Sheet1.Select
Sheet1.Range("A65536").End(xlUp).Select
GetLastA = Selection.Row
End Function


Report •
Related Solutions


Ask Question