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