52 week to 12 month chart conversion

[rjplante]

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.

 

[rjplante]

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:

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
	I	J	K	L
1		January	August
2				Quarter
3	Date Range	1-Jan-2020	31-Aug-2020
4	Week:	1	36
Data

Cell Formulas
Range	Formula
J4:K4	J4	=WEEKNUM(J3)

Cells with Data Validation
Cell	Allow	Criteria
L3	List	=Quarter
J1:K1	List	=Month

Chart:

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.