Search and Count in a Excel sheet

Asked By 220 points N/A Posted on -
qa-featured

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
Best Answer by Simmy C-me
Answered By 0 points N/A #97906

Search and Count in a Excel sheet

qa-featured

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

             ' do your job here
End If
r = r + 1
Loop

Answered By 5 points N/A #97907

Search and Count in a Excel sheet

qa-featured

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

qa-featured

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?

Best Answer
Best Answer
Answered By 0 points N/A #97909

Search and Count in a Excel sheet

qa-featured

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 5 points N/A #97910

Search and Count in a Excel sheet

qa-featured

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

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

instead of just

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

Answered By 220 points N/A #97911

Search and Count in a Excel sheet

qa-featured

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

qa-featured

Welcome. Glad to know it helped.

Related Questions