Macro to count entries

Microsoft Excel 2003 (full product)
September 21, 2009 at 01:11:14
Specs: Windows XP, PENTIMUM -4/1GB
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.


See More: Macro to count entries

Report •

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
GetLastA = Selection.Row
End Function

Report •
Related Solutions

Ask Question