|
-
Jun 28th, 2018, 08:21 AM
#1
Thread Starter
New Member
Update source of chartData
I have 4 charts in a sheet called "Menu" that uses a sheet in the image bellow as source. All of them have the first column as the X axis and each chart uses one of the four other columns as Y axis.
Their sources are :
=menu_chart_data!$A$1:$B$17
=menu_chart_data!$A$1:$A$17;menu_chart_data!$C$1:$C$17
=menu_chart_data!$A$1:$A$17;menu_chart_data!$D$1:$D$17
=menu_chart_data!$A$1:$A$17;menu_chart_data!$E$1:$E$17
But this sheet can increase one row every month with another process so I need their source to be from the first row all the way down to the last row that is not a zero.
Can I do that with VBA?
-
Jun 28th, 2018, 04:39 PM
#2
Re: Update source of chartData
yes
you could do a find on the first column to macth the 0, then just set the source to match the range above
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed 
pete
-
Jul 12th, 2018, 03:31 PM
#3
Thread Starter
New Member
Re: Update source of chartData
How can I put the formula in the source?
-
Jul 14th, 2018, 01:18 AM
#4
Re: Update source of chartData
try like
Code:
Dim sh As Worksheet, ch As Chart, s As Series
Set s = ch.SeriesCollection(1)
Set fnd = sh.Range("a:a").Find(0, , , xlWhole)
addr = fnd.Offset(-fnd.Row + 1).Resize(fnd.Row - 1).Address
s.Formula = Left(s.Formula, InStr(s.Formula, "!")) & addr & Right(s.Formula, 3)
where you preset ch to your chart and sh to the sheet containing the data
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote