How to assign cells to .Values property in Excel chart?

[sjh]

Hi,

I am trying to create a multiple series bar chart in an Excel sheet. I got the following from recording a macro.

ActiveChart.SeriesCollection(1).Values = _
"=('Budget Report'!R10C3,'Budget Report'!R14C3,'Budget Report'!R18C3,'Budget Report'!R22C3)"

ActiveChart.SeriesCollection(2).Values = _
"=('Budget Report'!R11C3,'Budget Report'!R15C3,'Budget Report'!R19C3,'Budget Report'!R23C3)"

I would like to make the Cells reference used in Values property into a variable, so that it changes dynamically. My code below gives me an error. Please help!

ActiveChart.SeriesCollection(i).Values = wBudgetRpt.Cells(10, 3), wBudgetRpt.Cells(14, 3), wBudgetRpt.Cells(18, 3), wBudgetRpt.Cells(22, 3)

 

[Nelviticus]

It looks like the parameter used by .Values is a string, so you'll need to have a string variable and build it using your other variables, like this:

Dim sParameter as String
Dim sSheet as String
Dim lRow1 as Long
Dim lRow2 as Long
Dim lRow3 as Long
Dim lRow4 as Long
Dim lCol as Long

sSheet = "Budget Report"
lRow1 = 10
lRow2 = 14
lRow3 = 18
lRow4 = 22
lCol = 3

sParameter = "=('" & sSheet & "'!R" & CStr(lRow1) & "C" & CStr(lCol) & ",'" & _
	sSheet & "'!R" & CStr(lRow2) & "C" & CStr(lCol) & ",'" & _
	sSheet & "'!R" & CStr(lRow3) & "C" & CStr(lCol) & ",'" & _
	sSheet & "'!R" & CStr(lRow4) & "C" & CStr(lCol) & ")"

ActiveChart.SeriesCollection(1).Values = sParameter

I haven't tested it, but it should work.

N.

 

[sjh]

Thank you for your post! It worked.

-sjh

 

[SkipVought]

sjh,

You can make your chart dynamic WITHOUT VBA coding.

I outlined a method in thread68-662897.

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com