Automatic chart creation 1

[LScharf]

I'd like to automate an annual task: I have a spreadsheet with 5 columns of data, and I need to create 20 bar charts.

The data look like this:

DRG Hospital Discharges AvgCharges Title
15 OHC 7 $5,225 CVA
15 Mt A 8 $5,651 CVA
15 Copley 6 $5,895 CVA
15 Valley 3 $6,070 CVA

There are 20 DRGs, with varying numbers of hospitals per DRG. So I've created a bar chart for each DRG, showing the Avg Charge for each hospital as a bar, along with the number of discharges sitting on top of each bar (it's actually a separate series but with the offset set to 100 so they overlap).

It's very time-consuming to update the charts each year - each year, these things change:
- DRG (so the chart titles change)
- number of hospitals appearing in a chart (so the data source range changes)

I figure it should be possible to loop through my data and create a chart for each group of DRGs. I've pilfered some code posted in another thread (68-865846 - thanks dkathrens77!) but can't get past an error ('object variable or with block variable not set').

Public Sub cmdChart()

    Dim strActiveSheet As String    'name of the Active Sheet
    Dim arDRGChange() As String     'an array to hold all possible DRGs
    Dim firstDRG As Range           'address of first row for chart
    Dim lastDRG As Range           'address of last row for chart
    
    Dim DRGA As Integer            'rememeber a DRG
    Dim DRGB As Integer            'remember first DRG different from preceeding DRG
    
    Dim intJ As Integer     ' 1 to 1000 (lines on the worksheet
    Dim intI As Integer     ' 1 to 31   (possible dates in the month)
    
    On Error GoTo ErrorHandler
      
    Application.ScreenUpdating = False  'suppress jerky screen behavior
    strActiveSheet = ActiveSheet.Name
    intI = 1
    'Note the address of the first DRG entered
    '      = Range ("A11") in arDRGChange(1)
    '
    'Go down the column of DRGs, looking for
    '       the first cell where the DRG is different
    '       from the preceeding array element
    '
    ' Note the cell value in next available array element
    '       repeat this process to the last date entered on the sheet
    
    Worksheets("DRG Source Data").Range("$A$11:$A$1000").Select
    Range("A11").Activate          'the first DRG entered on this sheet
    DRGA = ActiveCell.Value 'remember the first DRG

    firstDRG = ActiveCell
    
    For intJ = 11 To 1000        'step down each line on the worksheet
                                ' looking for DRG changes
        Range("A" & intJ).Select
            Select Case Range("A" & intJ).Value
                Case Is = DRGA
                    'do nothing
                Case IsNull(Range("A" & intJ).Value) 'no more entries
                    Exit For
                Case Else
                    'Create a new sheet then chart based on range above
                    ActiveWorkbook.Worksheets.Add.Name = "DRG " _
                      & ActiveCell.Offset(-1, 0).Value
                    Sheets("DRG Source Data").Activate
                    lastDRG = ActiveCell.Offset(-1, 0)
'add code here for creating a chart
                    firstDRG = ActiveCell
                    DRGA = Range("A" & intJ).Value
                End Select
        Next
        
Sub_Exit:

    Application.ScreenUpdating = True
    Exit Sub

ErrorHandler:

    Application.ScreenUpdating = True
    MsgBox Err.Description, vbCritical, "Error: " & Err.Number
    GoTo Sub_Exit
    
End Sub

Ideally:
- the chart title would reflect the title column
- the charts would go into separate tabs, and the tab names would reflect the DRGs
- the bars would all be dark in color except for the one assigned to "Mt A" hospital


I know I'm asking a lot... any help is much appreciated.

 

[SkipVought]

Hi,

Consider making ONE chart with a MS Forms DropDown box to select the desired DRG. Then AutoFilter the source data using the selected value.

I do this all the time. Unless you are plotting different data (like cost or capacity) you're just displaying the same data by varying criteria (DRG) -- that's ONE chart.

1. Use Advanced Filter to generate a Unique list of DRG's

2. Name that list something like DRG_List

3. Use DRG_List as the Input Range (list fill range) of your DD Box

4. Link the DD Box Cell Link to a cell named SelectedDRG_Index

5. Your selected value is then
[tt]
=INDEX(DRG_List,SelectedDRG_Index,1)
[/tt]
and name this cell SelectedDRG

6. Use this value as the Criteria value for your AutoFilter in VBA code (macro record changing the AutoFilter Criteria value)

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[LScharf]

Thanks, Skip! That sounds like a great solution (provided all the formatting is retained each time the user selects a different DRG - something lacking in Pivot Charts).

I'm not clear on creating a chart with a DD box as filter. Do I create a standard chart, then add to it?

I managed to create a listbox (is that same as DD box?) and make it show my DRGlist, per your instructions. And I have a chart (whose data source is the entire range of all DRGs). But now I don't see how to make the filter for the chart.

-Lauri

 

[SkipVought]

Have you added AutoFilter to your source data?

Data/Filter/AutoFilter.

Have you macro recorded selecting a criteria for the DRG column?

When you have, post your code.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[LScharf]

Aha, I'm starting to see the light...

AutoFilter is in place. (Cool how that affects the chart.)

Macro recorded, then I changed the Criteria1:

Sub DrgFilter()

    Selection.AutoFilter Field:=1, Criteria1:=SelectedDRG_Index
End Sub

Seems like this macro should be triggered by a click or change event in the listbox, but I'm not finding that option???

 

[SkipVought]

If you have cells named

SelectedDRG_Index
SelectedDRG

the former is the one linked to your Forms DropDown Cell Link

the latter should have the folmula
[tt]
=INDEX(DRG_List,SelectedDRG_Index,1)
[/tt]
and then your code should read

Selection.AutoFilter Field:=1, Criteria1:=[SelectedDRG]

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[LScharf]

You said:

5. Your selected value is then

=INDEX(DRG_List,SelectedDRG_Index,1)

but this doesn't work; my DRG list contains 20 values from 15 to 359, so if SelectedDRG_Index equals 359, the formula doesn't find a value...

It works when my macro is

Range("A10:E616").AutoFilter Field:=1, Criteria1:=[SelectedDRG_Index]

But I get an error "AutoFilter method of Range class failed".

Thanks a lot for the help.

 

[SkipVought]

What VALUE is in the Cell Link, SelectedDRG_Index?

It should be a number between 1 and 20 if you have 20 DRG values in DRG_List.

THAT NUMERIC VALUE (1 to 20) is used to select a DRG value from the DRG_List, which is returned to a cell I named SelectedDRG
[tt]
=INDEX(DRG_List,SelectedDRG_Index, 1)
[/tt]
THEN...

the SelectedDRG is plugged into the Filter Criteria for the DRG column in your table, which you just told me is in rows 15 to 359.

Range("A10:E616").AutoFilter Field:=1, Criteria1:=[SelectedDRG]

EXCEPT, your CODE stated that your table range is something different (10 to 616)

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[LScharf]

Sorry, I meant the DRG column contains values equal to integers between 15 and 359 (with plenty o' gaps). There are 20 distinct DRG values. Each DRG has multiple rows (hospitals), so the range goes from row 10 to row 616.

When I click on a value in my listbox, SelectedDRG_Index shows that value (e.g., 359). "SelectedDRG_Index" is the Range Name I gave to cell J20. I put "J20" into the Cell Link Property of the listbox (it wouldn't accept "SelectedDRG_Index").

Sorry if I'm being dense!!

 

[SkipVought]

DRG_List should have 20 values.

DRG_List is the Input Range.

THEREFORE, SelectedDRG_Index should have values from 1 to 20 only.

For instance, here's a table that should describe the data I have as a small sample...
[tt]
DRG_List Index
17 1
25 2
78 3
245 4
789 5
[tt]
The if you select 78, the Selected DRG_Index would be 3 and the FORMUAL in SelectedDRG would lookup the 3[sup]rd[/sup] element in the DRG_List which is 78.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[LScharf]

No... if I select 78 in my listbox, SelectedDRG_Index returns 78.

So if SelectedDRG_Index contains the value that I want to AutoFilter on, can't I just use that in my VBA?

Which is what I did, and it works, except for that pesky "AutoFilter method of Range class failed" error every time.

 

[SkipVought]

You DID say that you were using a FORMS listbox and NOT a Control Toolbox?

Using my example as above, the Cell Link for 78 (the 3[sup]rd[/sup] item in the list) contains 3 NOT 78.

78 would be contained in SelectedDRG and THAT value is what you want to use in your AutoFilter Criteria.

Skip,
[sub]
[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?" [/sub]

 

[LScharf]

AHA! NOW it is!! Wow, now it works like a charm.

Thanks so much.