named range as source for chart 3

[avmeno]

i am working with a macro that will produce a custom chart -- i am reading the location of the first cell in the range from screen. I then want to input series in adjacent cells as source for chart -- how do i do this?

Example: if i read a1 as the location of my first cell in range my chart has three series b1:b5, d1:d13, f1:f5 -- if i read d10 as my first cell in range then my chart has three series e10:e15,g10:g23,h10:h15 how do i incorporate to the source for chart ?

i tried several suggestions both at microsoft site and on web nothing has worked so far

 

[SkipVought]

Hi,

I dont' understand the EXAMPLES you state.

1) in this example you have TWO DIFFERNT SIZES for these 3 ranges ( b1:b5, d1:d13, f1:f5)

2) then, how can d10 be the FIRST cell in the range?

3) all of the sudden we have 3 more ranges (e10:e15,g10:g23,h10:h15)

I am totally confused!

Are you trying to define "moving" ranges for your series?

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[avmeno]

skip,

I first write data into some cells and then create a chart using that data. This is to produce a custom chart that excel does not have. I am writing the data so that once i have the generic chart, i can modify the chart (after the execution of the macro) by modifying the data in the cells in the excel sheet. This is done inside a sheet with other information. So I have to tell where in the sheet the macro should the write the data. This is accomplished by having an input box in the macro. So if i input a1 in the input box then the data for the three series will be written in b1:b5, d1:d13 and f1:f5. But if i input a15 in the dialog box then the data will be written in b15:b20, d15:d28 and f15:f20. I would do this because i already have information in rows 1 to 14 so i cannot have the macro write the data for the generic chart to those cells. Once I have done this how will let the charts.add command recognize the location of the data? I hope i made myself clear. Note the size of the series has not changed. The first series has 5 values, second has 13 values and third has 5 values.

 

[kpetursson]

On the Sheet1 i have the data as follows

selected cell | 1 | | 2 | | 3 |
---------------------------------------------
| 3 | | 1 | | 4 |
---------------------------------------------
| 5 | | 3 | | 2 |
---------------------------------------------
| 3 | | 5 | | 5 |
---------------------------------------------
| 2 | | 3 | | 1 |

It doesn't matter where "selected cell" as long as the relevant data is in the same relative location.

Sub FancyChart()
'
' FancyChart Macro
' Macro recorded 2/17/2005 by Kevin Petursson
'

'
Dim temp As Variant

Dim StartCellCol As Integer
Dim StartCellRow As Integer

Dim Range1 As String
Dim Range2 As String
Dim Range3 As String
Dim AllRange As String

StartCellCol = ActiveCell.Column
StartCellRow = ActiveCell.Row

Range1 = SimpleIntToChr(StartCellCol + 1) & LTrim(Str(StartCellRow)) & ":" & _
SimpleIntToChr(StartCellCol + 1) & LTrim(Str(StartCellRow + 4))
Range2 = SimpleIntToChr(StartCellCol + 3) & LTrim(Str(StartCellRow)) & ":" & _
SimpleIntToChr(StartCellCol + 3) & LTrim(Str(StartCellRow + 4))
Range3 = SimpleIntToChr(StartCellCol + 5) & LTrim(Str(StartCellRow)) & ":" & _
SimpleIntToChr(StartCellCol + 5) & LTrim(Str(StartCellRow + 4))
AllRange = Range1 & "," & Range2 & "," & Range3
temp = Len(Range1)
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(AllRange), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub
Function SimpleIntToChr(Number As Integer) As String
'Convert a given interger between 1-26 to an Excel column letter.
'ie. 1=A, 26=Z, 5=E
Select Case Number
Case 1
SimpleIntToChr = "A"
Case 2
SimpleIntToChr = "B"
Case 3
SimpleIntToChr = "C"
Case 4
SimpleIntToChr = "D"
Case 5
SimpleIntToChr = "E"
Case 6
SimpleIntToChr = "F"
Case 7
SimpleIntToChr = "G"
Case 8
SimpleIntToChr = "H"
Case 9
SimpleIntToChr = "I"
Case 10
SimpleIntToChr = "J"
Case 11
SimpleIntToChr = "K"
Case 12
SimpleIntToChr = "L"
Case 13
SimpleIntToChr = "M"
Case 14
SimpleIntToChr = "N"
Case 15
SimpleIntToChr = "O"
Case 16
SimpleIntToChr = "P"
Case 17
SimpleIntToChr = "Q"
Case 18
SimpleIntToChr = "R"
Case 19
SimpleIntToChr = "S"
Case 20
SimpleIntToChr = "T"
Case 21
SimpleIntToChr = "U"
Case 22
SimpleIntToChr = "V"
Case 23
SimpleIntToChr = "W"
Case 24
SimpleIntToChr = "X"
Case 25
SimpleIntToChr = "Y"
Case 26
SimpleIntToChr = "Z"
Case Else
SimpleIntToChr = "ERROR"
End Select
End Function


Kevin Petursson

 

[SkipVought]

I would not use the ActiveCell. Rather pass the row & column to the sub in a Call. Keep Select and Activate methods to a MINUMUM. check out How Can I Make My Code Run Faster? faq707-4105

Sub FancyChart(StartCellRow As Integer, StartCellCol As Integer)
    Dim Range1 As Range
    Dim Range2 As Range
    Dim Range3 As Range
    Dim AllRange As Range

    Set Range1 = Range(Cells(StartCellRow, StartCellCol + 1), _
                        Cells(StartCellRow + 4, StartCellCol + 1))
    
    Set Range2 = Range(Cells(StartCellRow, StartCellCol + 3), _
                        Cells(StartCellRow + 4, StartCellCol + 3))
    
    Set Range3 = Range(Cells(StartCellRow, StartCellCol + 5), _
                        Cells(StartCellRow + 4, StartCellCol + 5))
    
    Set AllRange = Application.Union(Range1, Range2, Range3)
    
    Charts.Add
    With ActiveChart
        .ChartType = xlLineMarkers
        .SetSourceData Source:=AllRange, PlotBy:=xlColumns
        .HasTitle = False
        .Axes(xlCategory, xlPrimary).HasTitle = False
        .Axes(xlValue, xlPrimary).HasTitle = False
        .Location Where:=xlLocationAsObject, Name:="Sheet1"
    End With
End Sub

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[kpetursson]

Wow that a lot cleaner than mine. No wonder the macro I took this code from runs so slowly!

Thanks Skip


Kevin Petursson

 

[avmeno]

skip, kevin,

thanks a lot -- it works!!!!!