Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Excel: Code Modification to Count/Average to pull values in all sheets

Posted on 2011-02-22
Medium Priority
Last Modified: 2012-05-11
Greetings Experts!

This question refers to a previous post that delt with Count/Average. The question was resolved by adding to the spreadsheet the following:

Sub UpdateCntAvgTable
Modification to Sub UpdateChart_ArrayOnLBLostFocus
Added named range cPlotCntAvg
Added named range cPlotRange
Sheet "Executive Rollup Data"  JG:KN we added a range that would populate values based on what is selected in the listbox.
I have duplicated this process (JG:KN ) to the rest of the sheets, like instructed.  I am at the point where I need a way to have it populate the rest of the sheets and not just have the code looking at only the Executive Rollup Data sheet.

Currently, it is grabbing only what ID's are listed in Executive.  
Need assistance on what options are out there to get this do be individually called.
Thoughts are that we need to create named ranges for all sheets like cPlotCntAvg and cPlotRange, but the VBA code still need to have a case statement to tell it to look at the name of the sheet to run a specific version of the UpdateCntAvgTable.

Please see attached file...

Question by:Maliki Hassani
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 42

Accepted Solution

dlmille earned 2000 total points
ID: 34958208
Ok.  I didn't fix it all, so you'd have a chance to go through it and add to the other executive report pages.

I assumed Summary Data and Executive Rollup count/average reports were identical (re: pointed to the same data sheet).  I updated the code for Executive Rollup/Summary and also for Surveillance.  Hopefully, your review of this will enable updates for the other tabs.  Otherwise, just holler.

Ok - in the code there was some documentation about what could change to support multiple versions of this.

This was done in 4 steps:

1 - Deleted the globally scoped named ranges:  cPlotCntAvg, cPlotRange, and rPlottable.
2 - Created a local-scoped name for each of these ranges in the Executive Rollup Data sheet and the Surveillance sheet.  The ranges point to the same areas of the sheet, but the names are now LOCALLY scoped.  E.g., the same named range can exist on different sheets and when SHEET is ahead of the named range - e.g., 'Executive Rollup Data'!cPlotCntAvg, that means its pointing to THAT sheet, THAT range.  When you're done, you'll have as many cPlotCntAvg named ranges as there are sheets where you want this to happen - ALL must be locally scoped.  So when the name is created, instead of scope: Workbook, you need to pick the sheet you're on (and if you make a mistake, it won't work properly) - in the name manger, you can see the list of names and SCOPE - so just check them as you go.
3 - Went to the ListBox3 activeX control and set the fill range to 'Sheet'!rPlotTable - example 'Executive Rollup Data'!rPlotTable for Executive Rollup and Summary, and 'Surveillance'!rPlotTable for Surveillance

4 - Then, I modified the two routines, as below, to account for the precedent datasheet name ahead of the named range.  See lines 19, 21, 37 & 41 and the use of the variable 'datasheet'.  These were the code modifications.
Sub UpdateChart_ArrayOnLBLostFocus(lBox As Variant, dataRange As String, datasheet As String, chartSheet As String, chartID As String, obUsed As Boolean)
Dim lItem As Long
Dim lbItemCount As Integer
Dim myDateRng As Integer

    Application.EnableCancelKey = xlDisabled
    lbItemCount = 0
    If lBox.ListCount <> 0 Then 'only if something is selected, otherwise, do nothing
        For lItem = 0 To lBox.ListCount - 1 'interrogate the listbox

            If lBox.Selected(lItem) = True Then
                ReDim Preserve lbArray(lItem + 1) 'dynamically adds an item to the array
                lbArray(lbItemCount) = lBox.List(lItem) 'populates our array
                lbItemCount = lbItemCount + 1
            End If
        Next lItem
        Call UpdateGenericChartArray(lbItemCount, dataRange, datasheet, chartSheet, chartID, obUsed)
        myDateRng = Sheets(datasheet).Cells(2, Range(dataRange).Columns.Count - obMainChart).Value 'get the date range - if this will be in every summary then this could be dataSheet instead of "Executive Rollup Data"

        Range("'" & datasheet & "'!cPlotRange").Value = myDateRng

        Call UpdateCntAvgTable(lbItemCount, datasheet)
        'now fix listbox back, just in case
        For lItem = 0 To lBox.ListCount - 1
            lBox.Selected(lItem) = False
            For j = 0 To lbItemCount - 1
                If lBox.List(lItem) = lbArray(j) Then
                    lBox.Selected(lItem) = True
                End If
            Next j
        Next lItem
    End If

End Sub
Sub UpdateCntAvgTable(lbItemCount As Integer, datasheet As String)
Dim rPlot As Range
Dim lItem As Long

    Set rPlot = Range("'" & datasheet & "'!cPlotCntAvg")
    rPlot.ClearContents 'clear what was last plotted
    For lItem = 0 To lbItemCount - 1 'then rebuild the range
        rPlot.Cells(lItem + 1, 1).Value = lbArray(lItem)
    Next lItem

End Sub

Open in new window

That's it.  PS if your listbox control doesn't scroll, you have to re-create it.  I did that for one, but left the others alone.  They are quirky, yes?


Author Comment

by:Maliki Hassani
ID: 34961392
Excellent!  Almost done with it!  Great stuff..  I didn't even know about locally naming ranges, just workbook.  Like the way you have it LOOKING for the sheet name to fire!!  Thanks..  Closing

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

670 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