52 week to 12 month chart conversion

rjplante

Active Member
Joined
Oct 31, 2008
Messages
489
I have a vertical bar chart that has a bar for each week of the year (52). I have two cells for a start and end date above the chart. In each of the cells is a drop down menu for each month of the year. I also have a third drop down to select Q1-Q4. When the user hits the enter key after selecting the desired quarter, the start and end date fields update to reflect the months contained in the selected quarter.

I want a macro that will adjust the chart so that it displays the number of weeks corresponding to the start and end dates entered into the cells above. Some months have five weeks in them, and some have four. I don't know of a simple way to calculate the number of weeks in the selected months in the current year. If the start and end dates match (March to March), I want the chart to display only the number of weeks in the month of March.

I have three charts and I will have the macro update all three (or refresh all three) to reflect the start and end dates selected.

Thanks for the help on this.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

rjplante

Active Member
Joined
Oct 31, 2008
Messages
489
I have figured out how to get my weeks converted. Now I need to get my chart to update based on the cell inputs. I have included my macro and my chart below. When I run my macro I get the following error: Run-time error '-2147467259 (80004005)': Method 'MaximumScale' of object 'Axis' failed.

The line causes the error is: .MaximumScale = Worksheets("Data").Range("K4").Value

Macro:
VBA Code:
Sub RefreshChart()
 
With Worksheets("Data").ChartObjects("Chart 1").Chart.Axes(xlCategory)

    'Change maximum scale for Chart 1 on Data Worksheet to value in cell K4
    .MaximumScale = Worksheets("Data").Range("K4").Value

    'Change minimum scale for Chart 1 on Data Worksheet to value in cell J4
    .MinimumScale = Worksheets("Data").Range("J4").Value

End With

End Sub

Chart cells:
Sales Tracker 18-MAY-2020.xlsm
IJKL
1JanuaryAugust
2Quarter
3Date Range1-Jan-202031-Aug-2020
4Week:136
Data
Cell Formulas
RangeFormula
J4:K4J4=WEEKNUM(J3)
Cells with Data Validation
CellAllowCriteria
L3List=Quarter
J1:K1List=Month


Chart:
1589924410486.png


What do I need to do to fix this error so that the chart will update and only shoe me the weeks from 1-36 or whatever I have selected in J4 and K4?

Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,755
Messages
5,597,929
Members
414,193
Latest member
bb60

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top