The specified Dimension is not valid for the current chart type

[mshbhwn98]

Hi,

I have some code which loops through a range called rngAnalysisList and assigns value to variables. The range is a table list analyses. The code then copies a template results page ready to fill out. The results page has 3 graphs and a table on it.

I am getting the following error

The specified Dimension is not valid for the current chart type

When it gets to copying the template for the second time.

Here is my code


Private Sub test()
Dim i As Integer, j As Integer
Dim myRange As Range
Dim ImportTypeVal As String, EDMVal As String, AccVal As String, IDVal As String
Application.ScreenUpdating = False

Set myRange = wksModeller.Range("rngAnalysisList")
For i = 1 To myRange.Rows.Count
For j = 1 To myRange.Columns.Count
If j = 1 Then
IDVal = myRange.Cells(i, j).Value
ElseIf j = 5 Then
ImportTypeVal = myRange.Cells(i, j).Value
ElseIf j = 6 Then
EDMVal = myRange.Cells(i, j).Value
ElseIf j = 7 Then
AccVal = myRange.Cells(i, j).Value
Else
End If
Next j

Sheets("Template").Copy After:=Sheets("Modeller")
ThisWorkbook.VBProject.VBComponents("wksTemplate1").Name = "wksAnalysis" & IDVal
Sheets("Template (2)").Name = "Analysis" & IDVal

Next i


Application.ScreenUpdating = True
End Sub


Why would this be causing this error?

Thanks

[mshbhwn98]

I have now solved this problem. It turns out there is nothing wrong with the code (with respect to it working). It may be poorly written as I'm a newbie. But the IDVal taken from the source table was not unique hence the error. Apologies for wasting anyones times.

[SamT]

Look at this version and see what you think

Private Sub test()
'Integer will not handle all the rows on a sheet
'and forces VBA to convert it to a Long when using a Range
Dim i As Long
Dim ImportTypeVal As String, EDMVal As String, AccVal As String, IDVal As String

Application.ScreenUpdating = False

With Sheets("Modeller").Range("rngAnalysisList")
For i = 1 To .Rows.Count
IDVal = .Cells(i, 1).Value
ImportTypeVal = .Cells(i, 5).Value
EDMVal = .Cells(i, 6).Value
AccVal = .Cells(i, 7).Value

Sheets("Template").Copy After:=Sheets("Modeller")
If Not IDVal = "" Then Sheets(Sheets.Count).Name = "wksAnalysis" & IDVal
Next i
End With
Application.ScreenUpdating = True
End Sub

[mshbhwn98]

Apologies. I appreciate you taking the time to reply. I thought this part of my post

"which loops through a range called rngAnalysisList and assigns value to variables. The range is a table list analyses. The code then copies a template results page ready to fill out"

sufficiently explained what I was trying to do. So to avoid the same problem in the future how would I explain what I am trying to do? I am very greatful so I hope this comes across in the nicest possible way

Thanks

[SamT]

That is what that code does.
Loop thru each Row in Range(rngAnalysisList
with each Row (i) assign the Variables values by certain columns
For each Row, Copy the Template and name it according to the Column (1) and Row (i) of rngAnalysis.

It only looks different because I have a different style than you. Where you loop thru all the columns in each row of MyRange. I see that the same columns of myRange are used all the time.

Since myRange = Sheets("Modeller").Range("rngAnalysisList"), I just used Range("rngAnalysisList") directly

[mshbhwn98]

That's great. Thank you. I actually miss read the bit at the bottom of your post (see below) so was confused because i thought I had been clear.

"Please take the time to read the Forum FAQ (http://www.vbaexpress.com/forum/faq.php)
"Can you explain what are you trying to do, and not how you think you want to do it?" Paul H. (http://www.vbaexpress.com/forum/showthread.php?47641-INDIRECT()-function-doesn-t-work-quot-Can-t-Find-Object-or-Library-quot&p=297932&viewfull=1#post297932)"

I do have another question regarding the same bit of code. It copies the 3 charts when the sheet is copied and the charts have names chart 1, chart 2 and chart 3. Why would the new copied sheet have graphs called chart 2, chart 2 and chart 3? So chart 1 on the new sheet is now also called chart 2. I reference the charts in another piece of code to resize them so the code breaks because it cannot find Chart 1 on the new copied sheet.

Thanks for all your help

[SamT]

I suspect that those are not the actual Names, ie they might be (Names.) Confused yet.:dunno

Open VBA and make sure you can see the Project Explorer (press Ctrl+R) and the Properties Window (Press F4.)

Click on any Chart Object in the Project Explorer and look at the Name(Name) pair of the Chart.
now look at the Properties Window and look at the (Name) Property and the Name Property. Confused Again? :banghead:

Going back to the Project explorer Window, The Name(Name) pair is actually the CodeName(Tab Name) pair. Every Chart, Worksheet and Workbook have Code Names and common names. Common names are visible in Excel. You can only see Code names in VBA. If you reference an object with
Sheets("Sheet1") you are referencing its Tab Name. But Suppose Sheets("Sheet1") has the Code Name Sheet3? (It happens a lot.)

You can actually reference one of those objects with its Code Name. In this example (Sheets("Sheet1") or Code Name Sheet3)

With Sheet3

BTW, Workbooks don't have a "Tab" or Common Name until they are saved, but all workbooks have the Code Name "ThisWorkbook."

OK, Back to the Properties Window. In this window you can change both Names of any of those Objects. To change the Code Name of an Object, click the Box next to (Name) and enter a new Code Name. To change the Common or "Tab Name, click the Box next to Name (No Brackets) and enter the new name. I always refer to the Names in the Project explorer to know what I am doing with the names.

Coders refer to the common name as "Name" and to the Code Name as "Code Name."

Remember all that. Write it on the back of your hand.

I hope this helps you find the naming issue with your Charts.

[mshbhwn98]

Thanks SamT. I'm not following the instructions you've given me. So I have the Project Explorer and Properties Windows already open. The project explorer does not have any chart objects in it though. (I cannot see any). All I can see is the Sheets and ThisWorkbook. I also have a couple of Modules but that's it.
12256

Am I missing something?

Thanks

[Bob Phillips]

I do have another question regarding the same bit of code. It copies the 3 charts when the sheet is copied and the charts have names chart 1, chart 2 and chart 3. Why would the new copied sheet have graphs called chart 2, chart 2 and chart 3?

Probably you have embedded charts on a worksheet.

[mshbhwn98]

Does that mean they do not have codenames?

How do I change the name of the embeded Chart if this is the case? My code looks like this

Private Sub MoveCharts()
Application.ScreenUpdating = False

Dim top As Integer

If Rows("14:33").Hidden = True And Columns("F:R").Hidden = True Then
top = 270
Else
top = 600
End If

ActiveSheet.Shapes("Chart 1").top = top
ActiveSheet.Shapes("Chart 1").Left = 20
ActiveSheet.Shapes("Chart 1").Height = 300
ActiveSheet.Shapes("Chart 1").Width = 380

ActiveSheet.Shapes("Chart 2").top = top
ActiveSheet.Shapes("Chart 2").Left = 412
ActiveSheet.Shapes("Chart 2").Height = 300
ActiveSheet.Shapes("Chart 2").Width = 380

ActiveSheet.Shapes("Chart 3").top = top
ActiveSheet.Shapes("Chart 3").Left = 804
ActiveSheet.Shapes("Chart 3").Height = 300
ActiveSheet.Shapes("Chart 3").Width = 380
Application.ScreenUpdating = True
End Sub


So when the copy sheet is made from the first sub calling this sub fails as there is no longer a "Chart 1" available.

Many thanks