View Poll Results: what is expected here?

Voters
2. You may not vote on this poll
  • not aware of the ans

    1 50.00%
  • please help

    1 50.00%
Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2010
    Posts
    13

    Unanswered: Type mismatch error

    Hi,

    I am very new to VBA. I am trying to set a dynamic range using cells method & use the same to chart a graph, but the same fails with type mismatch error. Following is the code I am using for the same. This is a bit urgent. Please help.

    Sub updateGraphSheet(inputSheet As String)

    Dim rng1 As Range
    Dim rng2 As Range
    Dim iRow2 As Long
    Dim iRow1 As Long

    'MsgBox (inputSheet)
    'iRow2 = Worksheets(inputSheet).UsedRange.Rows.Count
    'MsgBox (iRow2)
    iRow2 = Sheets(inputSheet).Range("d65536").End(xlUp).Row
    'iRow2 = LastRow(Sheets(inputSheet))
    'iRow2 = 34
    'iRow2 = inputSheet.Range("d65536").End(xlUp).Row
    iRow1 = 4

    Set rng1 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "a"), Sheets(inputSheet).Cells(iRow2, "a")) ' this is where the error occurs

    Set rng2 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, "b"), Sheets(inputSheet).Cells(iRow2, "b"))

    Worksheets(2).ChartObjects("Set1_FAP").Activate
    ActiveChart.SeriesCollection(1).XValues = rng1
    ActiveChart.SeriesCollection(1).Values = rng2

    End Sub

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Have a nose here:
    Spreadsheet Page Excel Tips: Referring To Ranges In Your VBA Code
    Notice anything different what you supply to the CELLS property compared to on here? HINT - the error message gives you the answer.

  3. #3
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    I can't directly reproduce the error with the code posted! Passing a string representing a column letter into the column parameter is valid.

    The only way I could get a type mismatch was by passing a non-numeric (could not be coerced to a valid row number) string into the row parameter, but I don't see how that could happen within the code on the thread. When the error occurs I'd suggest you check the values of iRow1 and iRow2 in the locals window.

  4. #4
    Join Date
    Apr 2010
    Posts
    13
    Thanks for your help!!

    I could figure out the problem.

  5. #5
    Join Date
    Apr 2010
    Posts
    13
    Hi legg,

    Seems like passing the string was the issue, as I have already checked row1 & row2 vals. but strangely the same code works for me in a diff sheet where I don't explicitly use SheetName.Cells

    Thanks for your time.

  6. #6
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Hi,

    Thanks for the information. I usually like to know why an error is happening, but I cannot find a reason why passing "a" into the column parameter would cause a type mismatch error.

  7. #7
    Join Date
    Apr 2010
    Posts
    13
    even i would like to know why such a behavior for two diff sheets. but right now falling short of time. As target dates are too near. facing one more issue with teh same code. i have manually created static graphs & am using the VBA code to update the graphs. Strangely it works for most of the graphs but partially fails for one. and when I see the graph after that it has removed the series actually. It throws an error SeriesCollection of Chart object failed. Usually i have seen this error when the series is not present but this one looks diff, because the series is present & I am only trying to update the range. the code fails at the following line:

    ActiveChart.SeriesCollection(4).XValues = rng1

    Please do help!!1

  8. #8
    Join Date
    Sep 2008
    Location
    London, UK
    Posts
    511
    Are all the charts of the same type?
    Does rng1 reference a single cell?

    What is the error message?
    Which version of XL?

    Can you zip and attach the workbook (that way I can investigate the cause of that other error too!)?

  9. #9
    Join Date
    Apr 2010
    Posts
    13
    Thanks for your reply.

    I am not sure if I can send the workbook, need to check that with the team. I am using XL2003 and all the graphs are of same type. rng1 doesn't refer to single cell. It refers to a range that will change dynamically.

  10. #10
    Join Date
    Apr 2010
    Posts
    13
    Hi ,

    The previously mentioned issue regarding the graph update problem is solved. But I am back again with another issue. I want to create secondary Y-Axis using VBA. I tried setting the .AxisGroup property of SeriesCollection but it failed saying Unable to set AxisGroup property of SeriesCollection class.

    Please help!!!

  11. #11
    Join Date
    Apr 2010
    Posts
    13

    Problem with dynamicalling plotting graph along secondary axis

    Hi,

    I am using following piece of code to update a chart,

    ActiveChart.SeriesCollection(4).Select
    ActiveChart.SeriesCollection(4).ChartType = xlLineMarkers
    ActiveChart.SeriesCollection(4).AxisGroup = 2
    ActiveChart.SeriesCollection(4).XValues = rng1
    ActiveChart.SeriesCollection(4).Values = rng11

    where rng1 & rng2 are calculated dynamically using the following piece of code:

    Set rng1 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, 1), Sheets(inputSheet).Cells(iRow2, 1))

    Set rng11 = Sheets(inputSheet).Range(Sheets(inputSheet).Cells( iRow1, 158), Sheets(inputSheet).Cells(iRow2, 158))

    The problem is sometimes this works, but sometimes it doesn't. It doesn't throw an error but I can't even see the plot though value is present. I am using XL 2003. Please help as this is too urgent!!!

Posting Permissions

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