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

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" wrote in
message ...

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

Many thanks Tom!

It works ! I am going to try to understand more this style of
programming.... mine is too simple and do not work each time!

Many Many thanks!

Sibilia




Tom Ogilvy Wrote:
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" wrote
in
message ...

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



--
Sibilia
------------------------------------------------------------------------
Sibilia's Profile: http://www.excelforum.com/member.php...o&userid=21363
View this thread: http://www.excelforum.com/showthread...hreadid=384181