Macro looping

[ApolloID]

Hi, i need to make one loop macro from all this macros.

This is the macro 1:

Sub Macro3()
Dim myrange As Range
Set myrange = Range("E4:P4,E5:P5")
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=myrange
    ActiveChart.ChartType = xlLineMarkers
End Sub

Macro 2

Sub Macro3()
Dim myrange As Range
Set myrange = Range("E4:P4,E8:P8")
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=myrange
ActiveChart.ChartType = xlLineMarkers
End Sub

macro 3

Sub Macro3()
Dim myrange As Range
Set myrange = Range("E4:P4,E11:P11")
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=myrange
ActiveChart.ChartType = xlLineMarkers
End Sub

macro 4

Sub Macro3()
Dim myrange As Range
Set myrange = Range("E4:P4,E14:P14")
ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=myrange
ActiveChart.ChartType = xlLineMarkers
End Sub

Basically the only difference is the range that offsets with three rows.

Can this be done?
Thanks in advance

 

[Trevor G]

Do you mean you need all of the macros to run, if so then create a macro to call each one.

Sub CallCharts()
Call Macro3
Call Macro4
Call Macro5
End Sub

 

[Ruddles]

I don't understand what you mean by "loop macro". Do you just want to combine these macros into a single one?

If so, just combine the ranges into a single Set and run the following code:-

Sub Macro99()
  Dim myrange As Range
  Set myrange = Range("E4:P5,E8:P8,E11:P11,E14:P14")
  ActiveSheet.Shapes.AddChart.Select
  ActiveChart.SetSourceData Source:=myrange
  ActiveChart.ChartType = xlLineMarkers
End Sub

No looping though.

Is that what you mean?

 

[Jaafar Tribak]

Or maybe you mean having a generic macro that accepts different chart source ranges like this :

Sub AddChart(ByVal Range As Range)
    Dim myrange As Range
    Set myrange = Range
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=myrange
    ActiveChart.ChartType = xlLineMarkers
End Sub

Sub Test()

    AddChart Range("E4:P4,E5:P5")

End Sub

 

[ApolloID]

Hi, this is what i need. I have a table like this:

4.....jan...feb...mar...apr...may...jun...jul...aug...sep...nov...dec
'macro to insert chart
5.......50....0.....10.....45.....90....17...20.....5......5.......48...3
6...space
7...'macro to insert chart
8.......2.....4......18......6......12....6....18....18.....2.......10...10
9....
10...'macro to insert chart
11.......125..2......500....178...36...12.....55...42....32.......2....3
12...
13...'macro to insert chart
14.......25....25.....87.....52.....44...25.....10...88....56....37...71
15...
16...'macro to insert chart
17......45.....54....64......54..........85....10....10....9.....9.....14
18...
19...'macro to insert chart
20.......3......4.....8........45.....89....25.....52....45...84...52....11
'macro to insert chart
....
....
900....100...120..5........0........0.....0......200...0...0...50.....100

i need a macro to insert a chart, but the range to change and offset with three rows.
Ex:
chart 1 Range("E4:P4,E5:P5")
chart 2 Range("E4:P4,E8:P8")
chart 3 Range("E4:P4,E11:P11")
chart 4 Range("E4:P4,E14:P14")
chart 5 Range("E4:P4,E17:P17")
......and so on.

I need to insert a macro to each row with data. Chart data range to be from row 4 with (the one with months) and the row with datas. and then to offset the row with datas by three rows. and again, and again....

 

[ApolloID]

How can i change only the range in this macro everytime is looping?

Sub Loop2()
    Do
Dim myrange As Range
Set myrange = Range("E4:P4,E5:P5")
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=myrange
    ActiveChart.ChartType = xlLineMarkers
    Loop Until IsEmpty(ActiveCell.Offset(2, 4))End Sub

Something like this:
Set myrange = Range("E4:P4,(E5:P5).offset(3, 0)")

Can this be done?

Thanks!

 

[Ruddles]

Jaafar's solution.

 

[ApolloID]

Hi, i looked at Jaafar's solution....but i cannot make it offset by 3 rows.
I need to offset only the second part of the range:

Range("E4:P4,(E5:P5).offset(3, 0)")
Any suggestion?

 

[Norie]

This might work, if I've got the pattern for the range references right.

Sub MacroX()
 
Dim myrange As Range
 
For I = 5 To 20 Step 3
 
    Set myrange = Union(Range("E4:P4"), Range("E" & I & ":P" & I))
 
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.SetSourceData Source:=myrange
    ActiveChart.ChartType = xlLineMarkers
 
Next I
 
End Sub

If set the loop to go to 20 so you can try it out, if it works you can increase that for how many sets of date there are.

Just noticed you had a similar idea, in fact probably a better one in an earlier post.

I don't think you got the format for the Offset right though.

Option Explicit
 
Sub Loop2()
Dim myrange As Range
Dim rng As Range
Dim rngVals As Range
 
    Set rng = Range("E4:P4")
 
    Set rngVals = Range("E5:P5")
 
    Do
 
        Set myrange = Union(rng, rngVals)
        ActiveSheet.Shapes.AddChart.Select
        ActiveChart.SetSourceData Source:=myrange
        ActiveChart.ChartType = xlLineMarkers
        Set rngVals = rngVals.Offset(3)
 
    Loop Until rngVals.Cells(1, 1) = ""
 
End Sub

 

[ApolloID]

Hi Norie,

The MacroX is working just fine.
I have one more question though... can each chart position be just below the it's offset range?

Thanks.