Error 1004 aplication defined or object defined error

Question

I am getting 1004 error in the following code: in the set range statement. Could you please let me know what am I missing here because i'm can't figure out.

Sub updatechart(Nombre As Variant)
Dim cht As Chart
Dim rng As Range
Dim cmb As ComboBox
Dim Fila As Integer
Dim ser As Series
Dim rng1 As Range
Dim rng2 As Range
Dim rngdata As Range
Dim s As Integer
Worksheets(2).Activate
Set cmb = Sheets(2).ComboBox1
Set cht = ActiveSheet.ChartObjects(1).Chart
Set rngdata = Sheets(1).Range("b6:f370")
semana = cmb.Value
Fila = BuscaPalabra1(semana)

For Each ser In cht.SeriesCollection
    ser.Delete
Next

For s = 3 To rngdata.Columns.Count
    Set ser = cht.SeriesCollection.NewSeries
    Set rng1 = Sheets(1).Range(Cells(Fila, s), Cells(Fila + 7, s))
    Set rng2 = Sheets(1).Range(Cells(Fila, 2), Cells(Fila, 2))

    With ser
        .XValues = rng2
        .Values = rng1
    'cht.SeriesCollection(contador).Select
   ' cht.SetSourceData Source:=Sheets("datos").Range("rng1", "rng2")

    End With

Next

End Sub

Is `Sheets(1)` a `Worksheet`? Or is it a `Chart`? If it is a chart, you won't be able to select a range of cells from it. — YowE3K, Oct 17 '16 at 19:13
Good point @YowE3K - you should always use the `Worksheets` collection when you want to be working with `Worksheet` objects. The `Sheets` collection contains more than just worksheets. — Mathieu Guindon, Oct 17 '16 at 19:17
@Mat'sMug - Using numerical indices for the Sheets and Worksheets collections is another pet hate of mine - it is soooo easy for the users to move sheets into a different order and then code just falls to pieces. — YowE3K, Oct 17 '16 at 19:20
Thanks for the advices YowE3K and Mat'sMug, i'm just a beginner in vba and still learning. I ca'nt figure out the reason of the error 1004 so i change the code — Carlos García, Oct 20 '16 at 14:14

score 1 · Answer 1 · answered Oct 17 '16 at 16:36

By default any call to Range() or Cells() from a regular code module (not a sheet code module) will use the ActiveSheet, so it's best to qualify all of those with a specific sheet:

With Sheets(1)
    Set rng1 = .Range(.Cells(Fila, s), .Cells(Fila + 7, s))
    Set rng2 = .Cells(Fila, 2)
End with

Seems like rng1 and rng2 are different sizes though?

Error 1004 aplication defined or object defined error

1 Answers1