2

In sheets("Cost"), there is an error commented in the code below. Once the error is resolved, I plan to put a 'c(x)' counter to loop through each chart

Run-time error '1004': Method 'Range' of object '_Worksheet' failed

I think the error has something to do with how I am referencing the other sheet

Dim p As Worksheet, cost As Worksheet
Set cost = Workbooks("dashboard.xlsm").Worksheets("Cost")
Set p = Workbooks("dashboard.xlsm").Worksheets("p")

Dim c(1 To 3) As Variant

p.Activate
'[c(x)] denotes ranges for each chart object
'modified by counter [i], denoting ranges for each series
Set c(1) = p.Range(Cells(3, 1 + i), Cells(3, 1 + i).End(xlDown))
Set c(2) = p.Range(Cells(3, 4 + i), Cells(3, 4 + i).End(xlDown))
Set c(3) = p.Range(Cells(3, 8 + i), Cells(3, 8 + i).End(xlDown))

cost.Activate
For i = 1 To 2
    With ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(i)
    'all charts follow same time series
    .XValues = p.Range(Range("a3"), Range("a3").End(xlDown)) '**error here**
    .Values = c(1)
    End With
Next i
mrod
  • 49
  • 1
  • 8
  • 1
    Try `.XValues = p.Range(p.Range("a3"), p.Range("a3").End(xlDown))` so that all ranges are qualified with a sheet. – SJR Jul 12 '17 at 15:48
  • That's perfect; it worked. However, now I am having the issue that c(1) on loop for series i=1 is being deleted and overwritten by c(1) on loop for series i=2. Any suggestions? – mrod Jul 12 '17 at 15:58
  • Neither your XValues or Values change in each iteration of the loop - is that right? – SJR Jul 12 '17 at 16:04
  • Scratch that, the issue is that the Xvalues are reading in as XValues and Values for both series – mrod Jul 12 '17 at 16:05
  • XValues are constant, Values should pass in i=1 or i=2 to c(1) depending on series collection, where they will reference separate columns. I have also qualified the c(1) ranges with a p. address as you have suggested earlier – mrod Jul 12 '17 at 16:06
  • Problem solved? – SJR Jul 12 '17 at 16:09
  • not quite, XValues are still reading in as XValue and Values when Values should change based on column Cells(3, 1 + i), from range c(1) – mrod Jul 12 '17 at 16:15
  • I think I referred to that in my second comment. I can't remember chart syntax, but Values is always c(1) in your code. – SJR Jul 12 '17 at 16:19
  • yes, there is simply something wrong with my syntax, which I cannot figure out for what I'm trying to do. Though you did solve my original problem where the sheet wasn't being properly qualified. I could write it with less loops but it won't be as concise. I ultimately wanted to nest it in another for loop, something like: x = 1 to 3 for ChartObjects("Chart " & x) .Values = c(x) to reference each chart. Relatively new to VBA so unsure why the 'i' value for the series is not passing into c(1) to reference two different columns – mrod Jul 12 '17 at 16:28
  • Don't you need something like `.Values = c(i)`. – SJR Jul 12 '17 at 16:33

1 Answers1

0

Try replacing your line:

.XValues = p.Range(Range("a3"), Range("a3").End(xlDown))

with:

.XValues = "=" & p.Range(p.Range("a3"), p.Range("a3").End(xlDown)).Address(False, False, xlA1, xlExternal)
Shai Rado
  • 33,032
  • 6
  • 29
  • 51