PDA

View Full Version : Solved: Charts using vba, works with Excel 2007 but error with previous versions.



kalosmail
12-08-2008, 04:45 PM
Hi All,

Im new here, I discovered this page thanks to google, and it has helped me a lot, but I have a problem that I couldnt found.

The code is made to update a graphic depending on some parameters chosen in the excel sheet.

The problem is that it doesnt work in previous versions of excel (i made it in Excel 2007 with save as 97-2003 document). It specifically said that its a Type Missmatch...

The Code is the following.

byw Thanks!

Dim Chart1 As ChartObject
Dim n1 As Integer
Dim n As Integer
Dim n2 As Integer
Dim Rango1 As String
Dim Rango2 As String
Dim Valor1 As String
Dim Valor2 As String
Dim ini As Integer
Dim fin As Integer
Dim Rango3 As Range
Dim Rango4 As Range
Dim Rango5 As Range
Dim Rango6 As Range
Sub Actualizar_Grafica()
Worksheets("Gr?ficas-PiezaFJ").ChartObjects(1).Delete
If Worksheets("Gr?ficas-PiezaFJ").Cells(3, 4).Value = "Pieza por FJ" Then
ini = Worksheets("Gr?ficas-PiezaFJ").Cells(4, 5).Value + 2
fin = Worksheets("Gr?ficas-PiezaFJ").Cells(4, 6).Value + 2
If Worksheets("Gr?ficas-PiezaFJ").Cells(3, 3).Text = "TODAS" Then
n = 39
n2 = 40
Else
For n1 = 41 To 165 Step 2
If Worksheets("Gr?ficas-PiezaFJ").Cells(3, 3).Text = Worksheets("Datos").Cells(1, n1).Text Then
n = n1
n2 = n1 + 1
Exit For
End If

Next n1
End If
Set Chart1 = ActiveSheet.ChartObjects.Add _
(Left:=20, Width:=800, Top:=50, Height:=350)

If Worksheets("Gr?ficas-PiezaFJ").Cells(3, 7).Text = "Cantidad" Then
Chart1.Chart.ChartType = xl3DColumnStacked
End If
If Worksheets("Gr?ficas-PiezaFJ").Cells(3, 7).Text = "Porcentaje" Then
Chart1.Chart.ChartType = xl3DColumnStacked100
End If
With Chart1.Chart.SeriesCollection.NewSeries
.Name = "='Datos'!R2C214"
'The problem appears to be here
.Values = Worksheets("Datos").Range(Worksheets("Datos").Cells(ini, n), Worksheets("Datos").Cells(fin, n))
.XValues = Worksheets("Datos").Range(Worksheets("Datos").Cells(ini, "AL"), Worksheets("Datos").Cells(fin, "AL"))
.ApplyDataLabels xlDataLabelsShowValue, LegendKey:=False, AutoText:=True
End With
With Chart1.Chart.SeriesCollection.NewSeries
.Name = "='Datos'!R3C214"
.Values = Worksheets("Datos").Range(Worksheets("Datos").Cells(ini, n2), Worksheets("Datos").Cells(fin, n2))
.XValues = Worksheets("Datos").Range(Worksheets("Datos").Cells(ini, "AL"), Worksheets("Datos").Cells(fin, "AL"))
.ApplyDataLabels xlDataLabelsShowValue, LegendKey:=False, AutoText:=True
End With
Chart1.Chart.HasTitle = True
Chart1.Chart.ChartTitle.Text = Worksheets("Gr?ficas-PiezaFJ").Cells(3, 3).Text
End If
End Sub

kalosmail
12-22-2008, 08:44 AM
Was my question confusing? o why I have no replies?, am I doing something wrong in the forum?...

Thanks

Benzadeus
12-22-2008, 11:40 AM
Could you post the workbook? In which line you get the error?

rbrhodes
12-22-2008, 06:13 PM
Excel chokes on:

Range(cells(X,X),cells(Y,Y))

It needs to be:

Range(cells(X,X),address,cells(Y,Y).address)

for Excel to build a range out of it. Most people will tell you this is not true but I'm not most people!

Try this:




'The problem appears to be here
.Values = Worksheets("Datos").Range(Worksheets("Datos").Cells(ini, n).ADDRESS, Worksheets("Datos").Cells(fin, n).ADDRESS)
.XValues = Worksheets("Datos").Range(Worksheets("Datos").Cells(ini, "AL").ADDRESS, Worksheets("Datos").Cells(fin, "AL").ADDRESS)



or slightly shorter:



'The problem appears to be here
.Values = Worksheets("Datos").Range(Cells(ini, n).ADDRESS,Cells(fin, n).ADDRESS)
.XValues = Worksheets("Datos").Range(Cells(ini, "AL").ADDRESS,Cells(fin, "AL").ADDRESS)

kalosmail
01-05-2009, 11:40 AM
Thanks rbrhodes, I was in my holidays so I havent seen the response till today. I?ve made the correction in the code, and is working in Excel 2007 but still having problems with previous versions (I tried it in excel 2000). I am attaching the Workbook as suggested by Benzadeus.

Thanks again for your help. vbmenu_register("postmenu_171208", true);

rbrhodes
01-05-2009, 01:03 PM
Hi.

You've been caught again!

Instead of what I gave you, for this line (twice) use this:

.XValues = Worksheets("Datos").Range("AL" & ini & ":AL" & fin)

ie:


'MsgBox Valor1
With Chart1.Chart.SeriesCollection.NewSeries
.Name = "='Datos'!R2C214"
.Values = Worksheets("Datos").Range(Cells(ini, n).Address, Cells(fin, n).Address)


'//Here
.XValues = Worksheets("Datos").Range("AL" & ini & ":AL" & fin)


'.Values = Worksheets("Datos").Range(Worksheets("Datos").Cells(ini, n), Worksheets("Datos").Cells(fin, n))
'.XValues = Worksheets("Datos").Range(Worksheets("Datos").Cells(ini, "AL"), Worksheets("Datos").Cells(fin, "AL"))
.ApplyDataLabels xlDataLabelsShowValue, LegendKey:=False, AutoText:=True
End With
With Chart1.Chart.SeriesCollection.NewSeries
.Name = "='Datos'!R3C214"
.Values = Worksheets("Datos").Range(Cells(ini, n2).Address, Cells(fin, n2).Address)


'// and here
.XValues = Worksheets("Datos").Range("AL" & ini & ":AL" & fin)


'.Values = Worksheets("Datos").Range(Worksheets("Datos").Cells(ini, n2), Worksheets("Datos").Cells(fin, n2))
'.XValues = Worksheets("Datos").Range(Worksheets("Datos").Cells(ini, "AL"), Worksheets("Datos").Cells(fin, "AL"))
.ApplyDataLabels xlDataLabelsShowValue, LegendKey:=False, AutoText:=True
End With
Chart1.Chart.HasTitle = True
Chart1.Chart.ChartTitle.Text = Worksheets("Gr?ficas-PiezaFJ").Cells(3, 3).Text

kalosmail
01-06-2009, 08:58 AM
Thank you so much rbrhodes, its working now!

:content: vbmenu_register("postmenu_172007", true);