Sheet/Range Array to loop through making graphs

Question

Apologies aside, my problem has become quite the curiousity and I can't find any answers as of yet.

A piece of my program is designed to search through a graph to see if a series already exists, and if it doesn't, it creates it. As of now the program is fine and dandy. HOWEVER, in doing so for 8 different graphs, the code is not only long, but could potentially be more inefficient. So, I decided to attempt to loop through an array. This is what one of the graphing blocks looks like:

EDIT: I didn't specifically say, but Z IS the name of a sheet AND the name of the series, as defined earlier in the code, so that is not an issue.

Sheets("A").Select
Count = ActiveChart.SeriesCollection.Count
Fail = 0
For c = 1 To Count
If ActiveChart.SeriesCollection(c).Name = Z Then
    With ActiveChart.SeriesCollection(c)
        .Values = Worksheets(Z).Range("AJ5:AJ45")
        .XValues = Worksheets(Z).Range("AP5:AP45")
    End With
    Exit For
    Fail = Fail - 1
End If
Fail = Fail + 1
Next c
If Fail = Count Then
ActiveChart.SeriesCollection.NewSeries
c = ActiveChart.SeriesCollection.Count
With ActiveChart.SeriesCollection(c)
    .Values = Worksheets(Z).Range("AJ5:AJ45")
    .XValues = Worksheets(Z).Range("AP5:AP45")
    .Name = Z
    .MarkerStyle = 1
    .MarkerSize = 9
End With
End If

(With 7 more of those underneath, with different sheets and Y values). SO, I tried this:

Dim SheetArr(0 To 7) As Sheets
Set SheetArr(0) = Sheets("A")
Set SheetArr(1) = Sheets("B")

etc..

Dim RangeArr(0 To 7) As Range
Set RangeArr(0) = Range("AJ5:AJ45")
Set RangeArr(1) = Range("AK5:AK45")

etc..

And starting a loop, replacing individual values with SheetArr(i) and RangeArr(i) respectively so only one block of code exists, but none of the graphs even begin plotting. Am I dimming arrays wrong, or is this just not possible?

(Sorry for a super long post, just trying to be as clear as possible.)

jmstoker · Accepted Answer · 2013-09-18 14:50:42Z

1

Instead of trying to create an array of sheets, which I'm not sure you can do, you can create an array of strings containing the sheet names.

Dim SheetNames(0 To 7) As String
SheetNames(0) = "A"
SheetNames(1) = "B"
...

Then use it like this

For IntSheet = 0 To 7
    ThisWorkbook.Sheets(SheetNames(IntSheet)).Select
    ...
Next

Address Comment About Ranges:

I stepped through the code below, testing out the range assignment and the appropriate ranges were selected

Dim RangeArr(0 To 7) As Range
Set RangeArr(0) = Range("AJ5:AJ45")
Set RangeArr(1) = Range("AK5:AK45")
RangeArr(0).Select   'Selects AJ5:AJ45
RangeArr(1).Select   'Selects AK5:AK45

edited Sep 18, 2013 at 14:50

answered Sep 18, 2013 at 13:58

jmstoker

3,5051 gold badge25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Joe Laviano Over a year ago

Thanks for that, that may have fixed the sheet problem. However, with the ranges, it seems to be inputting completely random ranges, different than what I put (for both X and Y values). i.e, my intended X and Y ranges for the first graph are $AP$5:$AP$50 and $AJ$5:$AJ50, but it types in $AW$5:$AW$50 and $AZ$5:$AZ$50. At first I thought it was some weird offset, but it isn't the same offset for each graph....

jmstoker Over a year ago

I've updated my solution with my test against your range code. I hope it helps. Also note I'm using Excel 2010.

Joe Laviano Over a year ago

Thanks for the help, but this issue may be beyond me at this point. It will select ranges perfectly, but when input to the graph the results come out completely different. If I choose AQ5:AQ40, it writes AX5:AX40..AH5:AH40 becomes AZ5:AZ40, I'm trying to come up with some pattern or reasoning but I can't...

jmstoker Over a year ago

Can you amend your question with actual code showing how you're using RangeArr()? Maybe I'm misunderstanding how you're using it.

Joe Laviano Over a year ago

I got it. I think it was because I wasn't referencing the sheet in my ranges, skewing them for whatever reason. At least that's my most reasonable idea, so thanks for your help!

Collectives™ on Stack Overflow

Sheet/Range Array to loop through making graphs

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related