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

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.

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.

Thanks for your help!!

I could figure out the problem.

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.

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.

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

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!)?

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.

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!!!

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!!!