Dynamic Chart On VBA

JuniorExcel · Jan 17, 2014

Hi everybody!
I request your help. I used 3 named ranges to create a dynamic chart on excel 2007.
The set of data are exported from a sheet to another through a combo box (user form).
It works from time to time because I often get an error message (after “upgrading” my user form for example) and have to redefine the chart's data source.
Do you know how to avoid that? Should I define those ranges on VBA, in that case how to do it?
Thank you for your attention.

Jon Peltier · Jan 17, 2014

What's your data look like?
What do the Name definitions look like?
What is the error message?

JuniorExcel · Jan 18, 2014

Hi !
Data Type:
The first column is date type (X-axis)
The two others columns are values (numbers Y-axis)

Name définitions:
Date=OFFSET(SHEET1!$A$4;;;COUNTA(SHEET1!$A:$A)-1)
Values1=OFFSET(SHEET1!$B$4;;;COUNTA(SHEET1!$B:$B)-1)
Values2=OFFSET(SHEET1!$C$4;;;COUNTA(SHEET1!$C:$C)-1)
And there is an offset of one line between the X-axis data range and the Y-axis data range when I highlight it on the chart. I am using excel 2007

Error message:

formula in this worksheet contains one or more invalid references.

I have to click again on the name of the sheet (sheet1) which is replaced by [0] to redefine the data source.

Thank you for your attention

Jon Peltier · Jan 18, 2014

I have found that including the arguments in the OFFSET function makes it more reliable:

Date=OFFSET(SHEET1!$A$4;0;0;COUNTA(SHEET1!$A:$A)-1;1)

Also, to ensure the ranges are the same length, I only count once, and use a different offset for the values:

Values1=OFFSET(Date;0;1)
Values2=OFFSET(Date;0;2)

These are not really the problem, probably, although Excel 2007's use of Names in a chart's data source was somewhat friable. The sign of this often was [0] in place of something meaningful.

That error message is frustrating, because it just indicates some formula somewhere is broken.

How do you transfer the data using a userform? If you just paste the data on top of existing data, it may break links, while using paste special or the VBA range2.Value = range1.Value does not.

JuniorExcel · Jan 19, 2014

After changing the name in charts data, the ranges are the same length now.
The error message still remains.
My values are in a listbox and I paste them like this:

Code:

Private Sub CommandPaste_Click()
Dim j As Integer
With Sheets("Sheet")
Line = .Range("D65536").End(xlUp).Row
.Range("A3:B" & Line) = ""
For j = 0 To Me.ListBox1.ListCount - 1
.Range("A" & j + 3) = CDate(Me.ListBox1.List(j, 2))
.Range("B" & j + 3) = CDbl(Me.ListBox1.List(j, 3))
Next j
End With
End Sub

Jon Peltier · Jan 19, 2014

Can you find a formula, either in the worksheet or in the chart, which displays the #REF! error?

JuniorExcel · Jan 20, 2014

Hi Jon,

Can you find a formula, either in the worksheet or in the chart, which displays the #REF! error?

.NO, I cannot.

The name ranges start from line 4. But the data are pasted from line 3. When I only paste one data from the listbox, I receive an error message. So I changed the line code in order to past those data from line 4. The error message still remains, but only when I past one line from the listbox, and I have to replace again the [0].

If it can give you a clue, I have a formula in column C (from C4:C200) which is linked to column B (from B4:B200). When I past the values from the code, I plot (depending on the length of the data), the date range, the values in B and C columns.

JuniorExcel · Jan 20, 2014

Hi!
I am back again. Is it possible to program an algorithm which returns the optimal value of a "constant" during an exponential smoothing for example?
I don’t want to test manually several values of that constant. Does Excel analysais Tools pack performs it?( I also heard about the Array MIn formula).
That what I am doing by pasting values from the userform to that sheet :a forecast.

Thanks

Jon Peltier · Jan 20, 2014

This is a totally different topic, which should have its own new thread.

JuniorExcel · Jan 24, 2014

Ok, Should I close the 1st topic before opening the new one? And in which category can I post it ?
Thanks!

Dynamic Chart On VBA

JuniorExcel

New Member

Excel Facts

Jon Peltier

MrExcel MVP

JuniorExcel

New Member

Jon Peltier

MrExcel MVP

JuniorExcel

New Member

Jon Peltier

MrExcel MVP

JuniorExcel

New Member

JuniorExcel

New Member

Jon Peltier

MrExcel MVP

JuniorExcel

New Member

Similar threads

Forum statistics

Share this page

Dynamic Chart On VBA

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

MrExcel MVP

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock