 Search and Count in a Excel sheet

Asked By 220 points N/A Posted on -

Hi,

I have an Excel sheet containing about 1,200 rows. Each of the rows has different customer information. I want to know how many of them have dues.

Problem is the number of rows is not fixed. They may vary. So I can’t count the number of empty cells and just minus it from total rows. Do you have any suggestion? SHARE Answered By 0 points N/A #97906

Search and Count in a Excel sheet

You don’t need to specify the limit in the loop. You can check for a column that is compulsory in the rows (always having value) as base and check whether it is blank.

If the specific column is blank then you can break it. Here is an example:

Do
If Sheet3.Cells(r, 1) = "" Then                               ' assuming that the 1st column is always not null
Exit Do
Else

End If
r = r + 1
Loop Answered By 0 points N/A #97907

Search and Count in a Excel sheet

If there is a serial number in the sheet. Then you can use it to control the loop. Answered By 220 points N/A #97908

Search and Count in a Excel sheet

Thanks but  the serial number in my table is like EF22310.  And I do not have any cell to track the total number of customer either.

The 1st row ID is compulsory. And the dues filed is in the 4th column. So what will it be? Answered By 0 points N/A #97909

Search and Count in a Excel sheet

Create an ActiveX button in the sheet. I am guessing your data is in sheet3.

In developer mode double click the button and paste this code

Dim r As Integer
Dim dues As Integer
r = 1
dues = 0
Do
If Sheet3.Cells(r, 1) = "" Then
Exit Do
Else
If Sheet3.Cells(r, 3) <> "" Then                                   ' substitute 3 with your column of dues.
dues = dues + 1
End If
End If
r = r + 1
Loop
MsgBox (dues) Answered By 0 points N/A #97910

Search and Count in a Excel sheet

If your dues column is of Number type. Then most likely you would use

If Sheet3.Cells(r, 2) <> "0" Then

If Sheet3.Cells(r, 2) <> "" Then Answered By 220 points N/A #97911

Search and Count in a Excel sheet

Thank you Simmy. Thanks goes to you too Angel.  You guys have been of real help. Answered By 0 points N/A #97912

Search and Count in a Excel sheet

Welcome. Glad to know it helped. 