Code Help – Plot Charts (2003)

Topic

New Reply

Dear Loungers,

I need big help.

I am given the above data. I need to save each row into a new file using the fund name (Column A), plot date (mmm-yy) against NAV line chart on a new Chart sheet (named “Chart”) and save each file.

I have managed to record macro to do the above but I need help: The end date is fixed, in this case, July 2007. Some records have very early date (starting X-value), making the x-axis labels really messy. Is it possible to do some kind of codes to “force” the x-axis to have only a max of 6 labels but not less than 4 labels.

Same goes to y-axis labels. There are some funds that go below 100. Is it possible to force the y-axis start value to be closest to the minimum value (round down and no decimal place) and the end value to be closest to the maximum value (round up and no decimal place). I also need to “force” the number of gridlines to have a max of 6 lines but not less than 4 lines.

Thank you very much in advance. Having this will save me nights of manually opening each files and adjusting the axes.

Regards
Rid

Reply | Quote

Replies

The workbook you attached doesn’t include the code you already have. It would be useful to see the macro, so that we know how exactly you create each chart.

Reply | Quote

Thanks Hans,

Below are the lines of codes that were mostly “recorded”. I am still trying to put everything together and make them do: create a new XLS file from each record, transpose the data (column A is now the dates, column B is the NAV data), plot the charts, fix those labels issues and save the files. All with a click of button. Can help?

Sub CopyTranspose()
Sheets(“NAV”).Select
Sheets(“NAV”).Move
Rows(“2:2”).Select
Selection.Delete Shift:=xlUp
Rows(“1:2″).Select
Selection.Copy
Sheets.Add
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Cells.Find(What:=”100”, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
End Sub

Sub PlotLineChart()
Dim myLineChart As Chart
Dim dBeginDate As Date
Dim dEndDate As Date
Dim intMonths As Integer
Application.ScreenUpdating = False
Const strTargetChartPath = “D:NotProcessed”
Range(“B1”).Select
newFileName = Selection.Cells.Value
newFileName = strTargetChartPath & newFileName
ActiveWorkbook.SaveAs Filename:=newFileName
Application.ScreenUpdating = False
Range(“B1”).Select
FinalPlotRow = Range(“B65536”).End(xlUp).Row
FinalPlotCell = “A1”
CompleteBlock = “B” & FinalPlotRow & “:A1”
Range(CompleteBlock).Name = “AreaToPlot”
MinimumValue = Application.Min(Range(“B:B”))
MinimumValue = Application.RoundDown(MinimumValue, -1)
MaximumValue = Application.Max(Range(“B:B”))
MaximumValue = Application.RoundUp(MaximumValue, -1)
MajorUnitValue = Application.Even(Application.RoundDown((MaximumValue – MinimumValue) / 4, 0))
‘ Beginning date.
Range(“B1”).Select
FinalRow = Range(“B65536”).End(xlUp).Row
FinalCell = “A” & FinalRow
dBeginDate = DateValue(Range(FinalCell))
‘ Ending Date.
dEndDate = DateValue(“31/7/2007”)
‘ Calculate number of months between dates.
intMonths = ((Year(dEndDate) – Year(dBeginDate)) * 12) + _
Month(dEndDate) – Month(dBeginDate)
‘ Display number of months.
NumberOfMonthValue = Application.RoundDown(Str$(intMonths) / 5, 0)
If NumberOfMonthValue <= 0 Then
NumberOfMonthValue = 1
End If
Set SourceData = ActiveSheet.Range("AreaToPlot")
Set myLineChart = Charts.Add
With myLineChart
.ChartType = xlLine
.SetSourceData Source:=SourceData, PlotBy:=xlColumns
.HasLegend = False
.Location Where:=xlLocationAsNewSheet
.HasTitle = False
End With
ActiveChart.Axes(xlCategory).Select
With ActiveChart.Axes(xlCategory)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
.BaseUnitIsAuto = True
.MajorUnit = NumberOfMonthValue
.MajorUnitScale = xlMonths
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.AxisBetweenCategories = False
.ReversePlotOrder = False
End With
With ActiveChart.Axes(xlValue)
.MinimumScale = MinimumValue
.MaximumScaleIsAuto = True
.MajorUnit = MajorUnitValue
.MinorUnitIsAuto = True
.Crosses = xlAutomatic
.ReversePlotOrder = False
.ScaleType = xlLinear
.DisplayUnit = xlNone
End With
Sheets("Chart1").Select
Sheets("Chart1").Name = "Chart"
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Data"
Sheets("Chart").Select
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub

Thank you in advance.

Reply | Quote

Try the attached version of the code. I streamlined your code a bit.

Reply | Quote

Thank you so much…it works wonder.

Reply | Quote