Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 19th, 2008, 07:39 AM
Registered User
 
Join Date: Aug 2008
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Find out N/A data

I have a worksheet with the following structure:

Column A: Date (from 11/10/1996 - 31/12/2000)
Column B-Z: Data (Number)

In which some day's data are N/A.

I want to find out how many N/A records in each month, what am I supposed to do with VBA codes, thanks for all who offers me a suggestion.

 
Old August 19th, 2008, 08:15 AM
Friend of Wrox
Points: 3,060, Level: 23
Points: 3,060, Level: 23 Points: 3,060, Level: 23 Points: 3,060, Level: 23
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2005
Location: , , .
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Are the 'N/A' literal text or Error Codes

Cheers
Shasur

http://www.dotnetdud.blogspot.com

VBA Tips & Tricks (http://www.vbadud.blogspot.com)
 
Old August 19th, 2008, 08:52 AM
Registered User
 
Join Date: Aug 2008
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

The "N/A" records are entered as text by myself.

Thanks for your reply.

 
Old August 25th, 2008, 08:40 AM
Registered User
 
Join Date: Aug 2008
Location: Nanaimo, BC, Canada.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I am not sure if you have found a solution. I had trouble logging on to the forum for the past few days. Anyway, here are my notes:

I would add 2 columns. One to calculate the month number and the next using a COUNTIF formula to count the cells that contain "N/A" in each row.

At the bottom of your report I would add SUMIF formulas, one for each month, to count the totals by month from all rows.
e.g. in Cell AA1 add formula =MONTH(A1) & copy to all rows
e.g. in Cell AB1 add formula =COUNTIF(B1:Z1,"=N/A")
e.g. at the end of your data rows (this formula assumes 100 rows of data), add the SUMIF formulas for each month number =SUMIF(AA1:AA100,"=1",AB1:AB100), =SUMIF(AA1:AA100,"=2",AB1:AB100)

I hope I understood your problem.




Similar Threads
Thread Thread Starter Forum Replies Last Post
XSL file can't find elements in XML data? Mateo1041 XSLT 2 September 18th, 2008 09:37 AM
Where will I find the data costorceroy BOOK: Beginning VB.NET 2nd Edition/Beginning VB.NET 2003 1 October 26th, 2004 05:23 AM
Find a particular data using ResultSet bamboat_45 Java Databases 2 August 5th, 2004 12:54 AM
Find Common Data nbryson SQL Language 4 December 19th, 2003 05:25 AM
I can't find the sample code(C# Data Security..). snowstar Wrox Book Feedback 1 November 21st, 2003 12:39 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.