V
Viktor Ygdorff
OK I know I am a pain but I really do not know how to solve this and I am
FORCED to solve it (if it was up to me I would not be doing it in the first
place). I have a user form in which the user is to enter two dates; one start
date and one end date. The user then presses a button on the user form and my
macro is supposed to among other things search the spreadsheet and update the
chart. I have to do this with variables so direct cell references are out of
the question. In theory my program works (nice huh) . It searches the
spreadsheet, finds the correct cells, gets the values to put into the charts
and here it goes into trouble. Updating charts works sometimes but only after
having done a lot of things, I do not really know what. I delete some series
and add them back again etc. Usually though it gives me trouble. Since I
cannot figure out what is wrong or how to fix it, I will give you some idea
of my code and perhaps (if there is a god) you can help me.
Every chart has an index number and the input data is defined as follows:
If index = 4 Then
ReDim varWorksheetInfoArray(0 To 5)
varWorksheetInfoArray(0) = "Curexp"
varWorksheetInfoArray(1) = "Date"
strChartName = "Diagram 11"
These are what to find and where to find it for a unique chart.
varWorksheetInfoArray(0) = "Curexp" refers to a worksheet whereas the rest
of the array refers to the contents of cells. The last line refers to the
name of the chart in the Excel spreadsheet.
I then call a new sub and sends info along:
Call chartMaker(varWorksheetInfoArray, strChartName)
Private Sub chartMaker(ByRef varWorksheetInfoArray() As Variant,
strChartName As String)
Dim i, j, k As Integer
ReDim rng(1 To UBound(varWorksheetInfoArray)) As Range
I then transfer the data in the array to a new array in order to be able to
use the addresses (this might be unnecessary but never mind)
k = 1
Do While k < UBound(rng)
Set rng(k) =
Worksheets(varWorksheetInfoArray(0)).Cells.Find(varWorksheetInfoArray(k),
LookIn:=xlValues)
k = k + 1
Loop
I then search the desired spreadsheet in order to find the correct location
of the startdate.
I=1
Do Until IsEmpty(rng(1).Offset(i, 0)) = True Or rng(1).Offset(i, 0).text =
strStartDatumArray(1) = True
i = i + 1
Loop
The strStartDatumArray is populated by the dates that the user is give. It
works but I don’t think it is necessary to give code concerning that.
I then do the same in order to find the end value:
j = 1
Do Until IsEmpty(rng(1).Offset(j, 0)) = True Or rng(1).Offset(j, 0).text =
strSlutDatumArray(1) = True
j = j + 1
Loop
I then choose the appropriate sheet where all the existing charts are and
choose the correct chart.
Sheets("Rapport").Select
ActiveSheet.ChartObjects(strChartName).Activate
I then assign the x-values for the current chart:
ActiveChart.Axes(xlCategory).Select
With ActiveChart
..SeriesCollection(1).XValues =
Sheets(varWorksheetInfoArray(0)).Range(rng(1).Offset(i, 0).Address & ":" &
rng(1).Offset(j, 0).Address)
End With
I then assign the y-values:
k = 2
Do While k < (UBound(rng))
If Not IsEmpty(rng(k)) Then
ActiveChart.SeriesCollection(varWorksheetInfoArray(k)).AxisGroup
= 1
ActiveChart.SeriesCollection(varWorksheetInfoArray(k)).Values =
Sheets(varWorksheetInfoArray(0)).Range(rng(k).Offset(i, 0).Address & ":" &
rng(k).Offset(j, 0).Address)
ActiveChart.SeriesCollection(varWorksheetInfoArray(k)).AxisGroup
= 1
Else: End If
k = k + 1
Loop
In theory and sometimes in real life this actually works! Now the code
always finds the correct addresses for the start and end dates and all the
inputs for the charts are correct (there are no hidden blanks etc.) and
references etc. are fine. But the charts just won’t let me feed them with the
info (sometimes). It is possible that there are completely different ways of
doing this but there are so many charts that I cannot create the charts in my
macro nor can I spend one working day/chart as I do now in order to fix them
by some magic touch…I am aware that I am a big pain for all you Excel-people
but I am just so lost at this right now and I really do not know what to do
or how to do it so if some please of the kindness to help me I would be very
grateful. I would also like to thank everyone on the forum that has helped
get this far! Again any assistance is very much appreciated! Thank you all!
FORCED to solve it (if it was up to me I would not be doing it in the first
place). I have a user form in which the user is to enter two dates; one start
date and one end date. The user then presses a button on the user form and my
macro is supposed to among other things search the spreadsheet and update the
chart. I have to do this with variables so direct cell references are out of
the question. In theory my program works (nice huh) . It searches the
spreadsheet, finds the correct cells, gets the values to put into the charts
and here it goes into trouble. Updating charts works sometimes but only after
having done a lot of things, I do not really know what. I delete some series
and add them back again etc. Usually though it gives me trouble. Since I
cannot figure out what is wrong or how to fix it, I will give you some idea
of my code and perhaps (if there is a god) you can help me.
Every chart has an index number and the input data is defined as follows:
If index = 4 Then
ReDim varWorksheetInfoArray(0 To 5)
varWorksheetInfoArray(0) = "Curexp"
varWorksheetInfoArray(1) = "Date"
strChartName = "Diagram 11"
These are what to find and where to find it for a unique chart.
varWorksheetInfoArray(0) = "Curexp" refers to a worksheet whereas the rest
of the array refers to the contents of cells. The last line refers to the
name of the chart in the Excel spreadsheet.
I then call a new sub and sends info along:
Call chartMaker(varWorksheetInfoArray, strChartName)
Private Sub chartMaker(ByRef varWorksheetInfoArray() As Variant,
strChartName As String)
Dim i, j, k As Integer
ReDim rng(1 To UBound(varWorksheetInfoArray)) As Range
I then transfer the data in the array to a new array in order to be able to
use the addresses (this might be unnecessary but never mind)
k = 1
Do While k < UBound(rng)
Set rng(k) =
Worksheets(varWorksheetInfoArray(0)).Cells.Find(varWorksheetInfoArray(k),
LookIn:=xlValues)
k = k + 1
Loop
I then search the desired spreadsheet in order to find the correct location
of the startdate.
I=1
Do Until IsEmpty(rng(1).Offset(i, 0)) = True Or rng(1).Offset(i, 0).text =
strStartDatumArray(1) = True
i = i + 1
Loop
The strStartDatumArray is populated by the dates that the user is give. It
works but I don’t think it is necessary to give code concerning that.
I then do the same in order to find the end value:
j = 1
Do Until IsEmpty(rng(1).Offset(j, 0)) = True Or rng(1).Offset(j, 0).text =
strSlutDatumArray(1) = True
j = j + 1
Loop
I then choose the appropriate sheet where all the existing charts are and
choose the correct chart.
Sheets("Rapport").Select
ActiveSheet.ChartObjects(strChartName).Activate
I then assign the x-values for the current chart:
ActiveChart.Axes(xlCategory).Select
With ActiveChart
..SeriesCollection(1).XValues =
Sheets(varWorksheetInfoArray(0)).Range(rng(1).Offset(i, 0).Address & ":" &
rng(1).Offset(j, 0).Address)
End With
I then assign the y-values:
k = 2
Do While k < (UBound(rng))
If Not IsEmpty(rng(k)) Then
ActiveChart.SeriesCollection(varWorksheetInfoArray(k)).AxisGroup
= 1
ActiveChart.SeriesCollection(varWorksheetInfoArray(k)).Values =
Sheets(varWorksheetInfoArray(0)).Range(rng(k).Offset(i, 0).Address & ":" &
rng(k).Offset(j, 0).Address)
ActiveChart.SeriesCollection(varWorksheetInfoArray(k)).AxisGroup
= 1
Else: End If
k = k + 1
Loop
In theory and sometimes in real life this actually works! Now the code
always finds the correct addresses for the start and end dates and all the
inputs for the charts are correct (there are no hidden blanks etc.) and
references etc. are fine. But the charts just won’t let me feed them with the
info (sometimes). It is possible that there are completely different ways of
doing this but there are so many charts that I cannot create the charts in my
macro nor can I spend one working day/chart as I do now in order to fix them
by some magic touch…I am aware that I am a big pain for all you Excel-people
but I am just so lost at this right now and I really do not know what to do
or how to do it so if some please of the kindness to help me I would be very
grateful. I would also like to thank everyone on the forum that has helped
get this far! Again any assistance is very much appreciated! Thank you all!