+ Reply to Thread
Results 1 to 8 of 8

worksheetfunction countifs, counting down and across, application defined error

Hybrid View

  1. 08-07-2012, 02:17 PM #1
    Lifeseeker
    Lifeseeker is offline
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    worksheetfunction countifs, counting down and across, application defined error

    Hi there,

    I am trying to come up with a VB solution that counts records based on conditions in VB. I have attached the file.

    In "Quarterly reports ALL" sheet, you see a table and ONLY B3 to Q19 need values to be populated by VB.

    The "IC collection" is where data gets collected.

    The following code is countifs using VB, but it's not quite working.

    n is the row counter on the report page while r is the column counter on the report page.

    Private Sub CommandButton1_Click()

Dim n As Long
Dim r As Long

lastRowData = Worksheets("IC Data Collection").UsedRange.Rows.Count
For n = 4 To 20
    For r = 2 To 16
    Worksheets("Quarter Reports ALL").Cells(n, r) = Application.WorksheetFunction.CountIfs(Worksheets("IC Data Collection").Range("H"), Worksheets("Quarterly Reports ALL").Cells(n, 1), Worksheets("IC Data Collection").Range("E"), Worksheets("Quarterly Reports ALL").Cells(3, r))
    
    Next r
    Next n

    End

End Sub
    When I run the code, I get the application-defined error. I must have got the range target wrong or some sort of syntax error, but I'm not sure.

    Anyone able to assist?

    Thanks
    Attached Files Attached Files
    Last edited by Lifeseeker; 08-16-2012 at 11:57 AM.
    Reply With QuoteRegister To Reply
  2. 08-07-2012, 02:43 PM #2
    rvasquez
    rvasquez is offline
    Valued Forum Contributor
    Join Date
    03-23-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    1,093

    Re: worksheetfunction countifs, counting down and across, application defined error

    Hello there,
    What are you trying to count? The total number of records in IC Data Collection whose values in what column are equal to what? in the Quarterly Reports ALL worksheet?

    Thanks!
    Reply With QuoteRegister To Reply
  3. 08-15-2012, 04:07 PM #3
    Lifeseeker
    Lifeseeker is offline
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: worksheetfunction countifs, counting down and across, application defined error

    Yes, I am counting the total number of records in the IC data collection based on conditions specified in the quarterly reports ALL worksheet. The "Quarter Reports ALL" worksheet is just a report template that's used to populate numbers.

    An example would be...if you go to "Quarterly reports ALL", B4 would be the total number of counts for AAA and Upper Respiratory that has been collected in the "IC data collection". C4 is the total number of records collected for BBB and Upper Respiratory, B9 would be the total number of records collected for AAA and GI, etc.

    I thought I would use two loops, one for the row down and the other for the column across, but it's not working at this stage.

    Is anyone able to assist?

    Thanks
    Last edited by Lifeseeker; 08-15-2012 at 04:11 PM. Reason: to be more specific about what I'm trying to count
    Reply With QuoteRegister To Reply
  4. 08-16-2012, 09:27 AM #4
    Lifeseeker
    Lifeseeker is offline
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: worksheetfunction countifs, counting down and across, application defined error

    Anyone able to assist?

    Thanks
    Reply With QuoteRegister To Reply
  5. 08-16-2012, 09:50 AM #5
    JapanDave
    JapanDave is offline
    Forum Expert JapanDave's Avatar
    Join Date
    06-10-2008
    Location
    The grid, I got in!
    MS-Off Ver
    Excel 2010/13
    Posts
    1,696

    Re: worksheetfunction countifs, counting down and across, application defined error

    What does the AAA, BBB etc do, I don't follow? Could you place some data as to how you want it represented in both sheets?
    Be fore warned, I regularly post drunk. So don't take offence (too much) to what I say.
    I am the real 'Napster'
    The Grid. A digital frontier. I tried to picture clusters of information as they moved through the computer. What did they look like? Ships? motorcycles? Were the circuits like freeways? I kept dreaming of a world I thought I'd never see. And then, one day...

    If you receive help please give thanks. Click the * in the bottom left hand corner.

    snb's VBA Help Files
    Reply With QuoteRegister To Reply
  6. 08-16-2012, 10:09 AM #6
    Lifeseeker
    Lifeseeker is offline
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: worksheetfunction countifs, counting down and across, application defined error

    Sorry. Please see the attached.

    "IC data collection" is for data collection and "quarterly reports all" is where countifs are applied.

    So in essence, B1 of "quarterly reports ALL" first specifies which quarter to collect(col A of IC data collection), and those numbers in the matrix are as a result of the counts collected in "IC data collection". (B3 to G19 requires countifs in VB. Don't have to worry about row 21). For example, you see two 1's under AAA because AAA has 1 count of upper respiratory and 1 count of LRI on IC data collection. etc.

    AAA, BBB, CCC are PCU names(col E of IC data collection).

    Also note that we count ONLY the ones that are "HAI". So another condition for all the count is on the J col of "IC data collection". If a records is a "HAI", then it needs to be added to the count.

    Hope this is clear

    Thanks
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 08-16-2012, 11:39 AM #7
    Lifeseeker
    Lifeseeker is offline
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: worksheetfunction countifs, counting down and across, application defined error

    Anyone able to assist?

    Thanks
    Reply With QuoteRegister To Reply
  8. 08-16-2012, 11:58 AM #8
    Lifeseeker
    Lifeseeker is offline
    Valued Forum Contributor
    Join Date
    10-21-2011
    Location
    Canada
    MS-Off Ver
    Excel 2010
    Posts
    513

    Re: worksheetfunction countifs, counting down and across, application defined error

    I think I just had it worked out.

    Below is the code for reference: 

    

Private Sub CommandButton1_Click()

Dim n As Long
Dim r As Long
Dim lastrowData As Long
lastrowData = Worksheets("IC Data Collection").UsedRange.Rows.Count

For n = 3 To 19
    For r = 2 To 7
    Worksheets("Quarterly Reports ALL").Cells(n, r) = Application.WorksheetFunction.CountIfs(Worksheets("IC Data Collection").Range("H2:H" & lastrowData), Worksheets("Quarterly Reports ALL").Cells(n, 1), Worksheets("IC Data Collection").Range("E2: E" & lastrowData), Worksheets("Quarterly Reports ALL").Cells(2, r))
    Next r
    Next n

    End

End Sub
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1