Wrong number of arguments or invalid property assignment

[guiltyberto]

What does this error mean and how can i fix it?

Set wb = Workbooks.Open(filename, True, True)

Do While Range("C" & i).Value <> ""
    Dim newChart As Chart
    Dim accName As String
    
    Set newChart = Charts.Add
    accName = Range("C" & i).Value
    
    MsgBox ("Got here")
    
    With newChart
        .ChartType = xlColumnClustered
        
        '.Location Where:=xlLocationAsObject, Name:="Accounts Chart"
        
        .SeriesCollection.NewSeries
        .SeriesCollection((i - 2) / 6).Name = accName
        MsgBox ("Got here 1")
        .Legend.Delete
        MsgBox ("Got here 2")
        .SeriesCollection((i - 2) / 6).Values = wb.Worksheets("Summary-Account").Range("E" & i & ":G" & i, "I" & i & ":K" & i, "M" & i & ":O" & i, "Q" & i & ":S" & i) 'error here
        MsgBox ("Got here 3")
        .SeriesCollection((i - 2) / 6).XValues = wb.Worksheets("Summary-Account").Range("E3:G3, I3:K3, M3:O3, Q3:S3")
        .HasTitle = True
        .ChartTitle.Text = accName
        MsgBox ("got here 3")
    End With
    
    i = i + 5
    
Loop

Thanks!!

 

[Chris Macro]

Which line is giving you the error?

 

[guiltyberto]

[COLOR=#333333].SeriesCollection((i - 2) / 6).Values = wb.Worksheets("Summary-Account").Range("E" & i & ":G" & i, "I" & i & ":K" & i, "M" & i & ":O" & i, "Q" & i & ":S" & i) 'error here

Right under MsgBox("Got here 2")

Sorry for the confusion[/COLOR]

 

[Chris Macro]

Can you tell us what the following outputs before the error occurs (insert before error line)

msgbox "E" & i & ":G" & i, "I" & i & ":K" & i, "M" & i & ":O" & i, "Q" & i & ":S" & i
msgbox (i-2)/6

 

[Teeroy]

When i is odd you can't get an integer value for the .SeriesCollection index, and with i accumulating by 5 each loop it will be odd every second loop.

 

[guiltyberto]

MsgBox ("E" & i & ":G" & i & ", " & "I" & i & ":K" & i & ", " & "M" & i & ":O" & i & ", " & "Q" & i & ":S" & i)

*Note: this code was edited and the commas added in, idk if its supposed to be .Range("string", "string", "string") or .Range("string, string, string") for the range function to work

eg .Range("E8:G8", "I8:K8", "M8:O8", "Q8:S8") vs .Range("E8:G8, I8:K8, M8:O8, Q8:S8")

yielded "E8:G8, I8:K8, M8:O8, Q8:S8"

MsgBox ((i - 2) / 6 & "")

yielded "1"

 

[guiltyberto]

When i is odd you can't get an integer value for the .SeriesCollection index, and with i accumulating by 5 each loop it will be odd every second loop.

It should be i = i + 6, sorry, I can't edit the original post anymore

 

[Teeroy]

The Range function as you are using it should be passed a single string with the comma's included in it. If you use a comma outside a string then the Range is expecting only 2 Arguments, both Range objects, to set the corners of a block Range.

 

[guiltyberto]

The Range function as you are using it should be passed a single string with the comma's included in it. If you use a comma outside a string then the Range is expecting only 2 Arguments, both Range objects, to set the corners of a block Range.

Worked perfectly, thank you so much!

 

[Teeroy]

You're welcome.