+ Reply to Thread
Results 1 to 3 of 3

Run-time error '13':Type mismatch

  1. #1
    Registered User
    Join Date
    03-21-2005
    Location
    UK
    Posts
    19

    Red face Run-time error '13':Type mismatch

    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

  2. #2
    Tom Ogilvy
    Guest

    Re: Run-time error '13':Type mismatch

    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
    >




  3. #3
    Registered User
    Join Date
    03-21-2005
    Location
    UK
    Posts
    19

    Thumbs up

    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




    Quote Originally Posted by Tom Ogilvy
    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
    >

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1