Sub Axis_Graph()
Dim startYear As Integer
Dim EndYear As Integer
Dim startYear2 As Integer
Dim EndYear2 As Integer
Dim StartLine As Integer
Dim EndLine As Integer
Dim rngX As Range
Dim rngY As Range
startYear = 1950 ' default value
EndYear = 2050 ' default value
startYear2 = Sheets("Incidence").Range("H9").Value ' choose by the
EndYear2 = Sheets("Incidence").Range("J9").Value ' choose by the user
StartLine = 3 + (startYear2 - startYear)
EndLine = 103 + (EndYear2 - EndYear)
Debug.Print StartLine, EndLine
With Sheets("Incidence")
Set rngX = .Range(.Cells(StartLine, "W"), _
.Cells(EndLine, "W"))
Set rngY = .Range(.Cells(StartLine, "X"), _
.Cells(EndLine, "X"))
.ChartObjects("Chart 32").Chart.SeriesCollection(1) _
.XValues = "=" & rngX.Address(True, True, xlR1C1, True)
.ChartObjects("Chart 32").Chart.SeriesCollection(1) _
.Values = "=" & rngY.Address(True, True, xlR1C1, True)
End With
End Sub
works for me.
--
Regards,
Tom Ogilvy
"Sibilia" <
[email protected]> wrote in
message news:
[email protected]...
>
> Hi all
>
> I am trying to make a VBA code for a Graph, giving the opprtunity to
> the user to change the X-axis and y-axis sources. the code change very
> well the first axis but block at the 2ndf axis with *Run-time error
> '13':Type mismatch * ;
>
> Here is my code:
>
>
> Sub Axis_Graph()
> Dim startYear As Integer
> Dim EndYear As Integer
> Dim startYear2 As Integer
> Dim EndYear2 As Integer
> Dim StartLine As Integer
> Dim EndLine As Integer
>
> startYear = 1950 ' default value
> EndYear = 2050 ' default value
> startYear2 = Sheets("Incidence").Range("H9").Value ' choose by the
> user
> EndYear2 = Sheets("Incidence").Range("J9").Value ' choose by the user
>
> StartLine = 3 + (startYear2 - startYear) 'Calculte the row number
> where is the StartYear2
>
> EndLine = 103 - (EndYear2 - EndYear) 'Calculte the row number where
> is
> the EndYear2
>
> With Sheets("Incidence")
> ChartObjects("Chart 32").Chart.SeriesCollection(1).XValues = _
> Range(.Cells(StartLine, "W"), .Cells(EndLine, "W"))
>
> CHARTOBJECTS(\"CHART 32\").CHART.SERIESCOLLECTION(1).VALUES = _
> RANGE(.CELLS(STARTLINE, \"X\"), .CELLS(ENDLINE, \"X\"))
> END WITH
>
> end sub
>
> Thanks
>
> Sibilia
>
>
> --
> Sibilia
> ------------------------------------------------------------------------
> Sibilia's Profile:
http://www.excelforum.com/member.php...o&userid=21363
> View this thread: http://www.excelforum.com/showthread...hreadid=384181
>
Bookmarks