Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update charts
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(va rWorksheetInfoArray(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 dont 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).Offs et(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).Offs et(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 wont 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Charts do not update | Excel Discussion (Misc queries) | |||
link excel charts to web pages and update charts automatically | Charts and Charting in Excel | |||
Update Charts | Excel Discussion (Misc queries) | |||
Charts won't update | Charts and Charting in Excel | |||
Update Charts using VBA | Excel Programming |