Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Feb 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code Help - Plot Charts (2003)

    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Help - Plot Charts (2003)

    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.

  3. #3
    Star Lounger
    Join Date
    Feb 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Help - Plot Charts (2003)

    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((MaximumVal ue - 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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code Help - Plot Charts (2003)

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

  5. #5
    Star Lounger
    Join Date
    Feb 2005
    Posts
    61
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code Help - Plot Charts (2003)

    Thank you so much...it works wonder.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •