Search and Count in a Excel sheet

Asked By Alisa 220 points N/A Posted on - 05/16/2011

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?

• Status: Open
• Question Views: 1340
• Answer Count: 7
• Vote Up 22 Vote Down

• Answer Accepted: Yes
• Question Category: MS Excel

Best Answer by Simmy C-me

Go To Solution

Answered By Simmy C-me 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

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

About Simmy C-me

Questions
0

Answers
16

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By AngelOfTech 5 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.

About AngelOfTech

Questions
0

Answers
25

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By Alisa 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?

About Alisa

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Best Answer

Best Answer

Answered By Simmy C-me 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)

About Simmy C-me

Questions
0

Answers
16

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By AngelOfTech 5 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

instead of just

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

About AngelOfTech

Questions
0

Answers
25

Best Answers
4

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By Alisa 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.

About Alisa

Questions
1

Answers
2

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel

Answered By Simmy C-me 0 points N/A #97912

Search and Count in a Excel sheet

Welcome. Glad to know it helped.

About Simmy C-me

Questions
0

Answers
16

Best Answers
6

• Vote Up 0 Vote Down

• Posted on - 05/16/2011
• Question Category: MS Excel