Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create charts - Points to wrong sheet
I have a macro in personal.xls that automatically creates charts. There are
three sheets, the first is where the user selects a name from a drop down box and the chart is displayed. The second sheet is, I think where the problem lies, it looks at the name the user has selected on sheet one and populates with the data drawn from sheet three. Sheet one then displays the graph. The problem I'm having is that the second sheet draws it's data from an earlier workbook (results - march) and not the one I wish to use (results - october). When I created the October sheet I copied the sheets across - the macros where in the workbook so I moved them to my personal.xls to make them work. The formula on the first column of the second sheet looks like this if it helps! =VLOOKUP(F2,'I:\CCR\SAM\IPP\FCC Results\March 2008\[Results - March.xls]Individual Results Data'!B1:N202,1,FALSE) I need March to read Octover but Excel says that one or more references are invalid! Grateful for any replies! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create charts - Points to wrong sheet
If it helps, here is the thisworkbook.makemycharts macro;
Sub MakeMyCharts() ' Dim ws As Worksheet Dim rng As Range Dim Yaddr As String Dim Y2addr As String Dim Xaddr As String Dim iRow As Long Dim iSrs As Long Dim iAddr As Long Dim cht As Chart Set ws = ActiveSheet If TypeName(Selection) < "Range" Then MsgBox "Select the data range for the charts" Exit Sub End If Set rng = Selection For iRow = 2 To rng.Rows.Count Set cht = Charts.Add cht.Name = rng.Cells(iRow, 1).Value & " - " & rng.Cells(iRow, 2).Value For iSrs = cht.SeriesCollection.Count To 1 Step -1 cht.SeriesCollection(iSrs).Delete Next With cht.SeriesCollection.NewSeries .Name = rng.Rows(iRow).Resize(1, 2) Yaddr = "=(" Y2addr = "=(" Xaddr = "=(" For iAddr = 3 To 9 Step 2 Yaddr = Yaddr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Y2addr = Y2addr & "'" & ws.Name & "'!" & rng.Cells(iRow, iAddr + 1).Address(ReferenceStyle:=xlR1C1) & "," Xaddr = Xaddr & "'" & ws.Name & "'!" & rng.Cells(1, iAddr).Address(ReferenceStyle:=xlR1C1) & "," Next Yaddr = Left$(Yaddr, Len(Yaddr) - 1) & ")" Y2addr = Left$(Y2addr, Len(Y2addr) - 1) & ")" Xaddr = Left$(Xaddr, Len(Xaddr) - 1) & ")" .Values = Yaddr .XValues = Xaddr .ChartType = xlLineMarkers End With With cht.SeriesCollection.NewSeries .Name = "Target" .Values = Y2addr .ChartType = xlColumnClustered End With Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to create charts - Points to wrong sheet
Actually, forget it! I've discovered it usually helps to ensure your sheets
are named correctly! I had neglected to rename the data sheet from Sheet 1 to Individual Results Data. Problem now sorted!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Button points to wrong macro | Excel Discussion (Misc queries) | |||
Macro to create charts | Charts and Charting in Excel | |||
Macro to create charts? | Charts and Charting in Excel | |||
Macro to create charts? | Excel Programming | |||
my macro is clearing the wrong sheet | Excel Programming |