Solved

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

Posted on 2011-02-22
2
237 Views
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...


 NOC-Reports-r26.xlsm
0
Comment
Question by:Maliki Hassani
2 Comments
 
LVL 41

Accepted Solution

by:
dlmille earned 500 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
    Else
    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?

Dave
NOC-Reports-r27.xlsm
0
 

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
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
My experience with Windows 10 over a one year period and suggestions for smooth operation
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

813 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now