[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Add statistic data using VBA

Posted on 2014-09-17
12
Medium Priority
?
112 Views
Last Modified: 2014-09-19
I am using VBA to analyse and create numerous worksheets. I need to add a worksheet and the insert the name from each sheet and the count of items [excluding the header row]

i.e.

Column A            Column B
Sheet 1                  250
Sheet 2                    75
Sheet 3                1450
Sheet 4                  600

etc

Could an expert provide me with the VBA code to do this please

Thanks in Advance
0
Comment
Question by:Jagwarman
  • 6
  • 6
12 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40327724
Hello,

try this:

Sub count()
For i = 1 To ActiveWorkbook.Sheets.count
Me.Range("A" & i) = ActiveWorkbook.Sheets(i).Name
On Error Resume Next
Me.Range("B" & i) = Worksheets("Sheet" & i).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count
If Err Then
Me.Range("B" & i) = 0
End If
On Error GoTo 0
Next i

End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 40327871
samo4fun

I am getting invalid use of Me Keyword
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40327880
Can you put the sheet name where you want the data to be written? i.e. Sheet1 instead of Me?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:Jagwarman
ID: 40327900
I tried that but got Object required. Having said that There can be 20 or 30 sheets depending on the days data so I cannot hard code that?
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40327971
Are you opening the file where you count the sheets and object or you are appending the new sheet and saving the results there?

Can you provide more detail steps on process so that i get clear picture? ;)
0
 

Author Comment

by:Jagwarman
ID: 40327989
My macro opens two files and then performs various functions. It sets up new Sheets for each Name in column F and copies over all rows with that respective name.

so today there may be 10 different names so 10 new sheets with their respective names tomorrow maybe 8 next day when the file is run maybe 30 etc

I then create a sheet call stats and on this sheet I need to  insert the name from each sheet and then count of items [excluding the header row]

 i.e.

 Column A            Column B
 Sheet 1                  250
 Sheet 2                    75
 Sheet 3                1450
 Sheet 4                  600

Regards
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40328063
OK... in the code after you create sheet "stats" you can add updated version of the code:

For i = 1 To ActiveWorkbook.Sheets.count
Sheets("stats").Range("A" & i) = ActiveWorkbook.Sheets(i).Name
On Error Resume Next
Sheets("stats").Range("B" & i) = Worksheets("Sheet" & i).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count
If Err Then
Sheets("stats").Range("B" & i) = 0
End If
On Error GoTo 0
Next i

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 40328105
I appreciate your help and apologise for keep returning :-)

Above code listed the sheets but...... all but one of the sheets in the totals it put 0 and in one it put 32

Sheet 1      0
Sheet 2      0
Sheet 3      0
Sheet 4      32
Sheet 5      0


in fact the totals should be

Sheet 1      0
Sheet 2      57
Sheet 3      28
Sheet 4      14
Sheet 5      14
0
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 40328872
No problems, as long as it takes to solve this issue...

Did you change the range in this line :

Sheets("stats").Range("B" & i) = Worksheets("Sheet" & i).Range("A:A").Cells.SpecialCells(xlCellTypeConstants).count

since it is only observing the column A and i presume that there is data in other columns so maybe you can change it to something like this :

Sheets("stats").Range("B" & i) = Worksheets("Sheet" & i).Range("A:AA").Cells.SpecialCells(xlCellTypeConstants).count
0
 

Author Comment

by:Jagwarman
ID: 40332154
Hi Samo4fun

Again apologies for the delay, been busy on other stuff.

Just tried this on another workbook and get

Sheet 1      0
 Sheet 2      0
 Sheet 3      0
 Sheet 4      0
 Sheet 5      0


should be

Sheet 1       0
 Sheet 2      1685
 Sheet 3      382
 Sheet 4      0
 Sheet 5      0

I only need to count in column A. Does it matter what is in the cell i.e. Text, Number, Date etc?

Thanks
Regards
0
 
LVL 15

Accepted Solution

by:
Haris Djulic earned 2000 total points
ID: 40332241
Hello,

can you try this.. I just tested it and it works ok..

For i = 1 To ActiveWorkbook.Sheets.count
Sheets("stats").Range("A" & i) = ActiveWorkbook.Sheets(i).Name
On Error Resume Next
Sheets("stats").Range("B" & i) = Worksheets("Sheet" & i).Range("A:A").Cells.count - WorksheetFunction.CountBlank(Worksheets("Sheet" & i).Range("A:A"))
If Err Then
Sheets("stats").Range("B" & i) = 0
End If
On Error GoTo 0
Next i

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 40332265
Hi samo4fun

it's something to do with my workbook. I copied all of the data onto a new workbook and it worked. Very odd. So thanks for all your work on this I will sign this one off now and look at my workbook to see if I can sus out the problem.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question