Officially confused - Union Range??

[Chuck6475]

So I'm feeling better about what I know, I thought. (I tip my hat to all of you that have this stuff down.)

Sub Shotgun()

' Builds the tee box array for a shotgun start in the TeeTimes page
' Reverse Shotgun starting with hole 1 and working backwards
' Par 4's & Par 5's have two groups - Par 3's have one group

Dim r1, r2, r3, CoursePar As Range

    With Sheets("Course")
        Set r1 = .Range("f6:m6")    'holes 2 to 9
        Set r2 = .Range("o6:w6")    'holes 10 to 18
        Set r3 = .Range("e6")       'hole 1
        
    End With
    
'Set up course par as 2 to 18, 1  - So the loop can go backwards

    Set CoursePar = Application.Union(r1, r2, r3)

Q: I expected that this would give me an array with holes 2 to 18, 1 in that order.

As you can tell by the question, it doesn't. In fact while the array says it has a count of 18, it only has 9 values in it. Namely r2.

Thoughts?

 

[JoeMo]

CoursePar is not an array. It's the union of 3 ranges and has two areas:
Areas(1) is the range e6:m6
Areas(2) is the range o6:w6

 

[Chuck6475]

In my terms you are saying that while I may have thought I could control the order the range cells are combined (via union) that isn't what happens. The union simply concatenates them all into one range.

With that my question is reduced to why are there only 9 values in the Value2 window of the Watches area, while the Count for the variable / object is 18?

 

[JoeMo]

In my terms you are saying that while I may have thought I could control the order the range cells are combined (via union) that isn't what happens. The union simply concatenates them all into one range.

With that my question is reduced to why are there only 9 values in the Value2 window of the Watches area, while the Count for the variable / object is 18?

The nine values come from the first area of the non-contiguous union range. That's e6:m6. The "missing" 9 are in the second area of the range, that's o6:w6. Try this in your code

For i = 1 To CoursePar.Areas.Count
MsgBox "Area " & i & " is " & CoursePar.Areas(i).Address
Next i

 

[Chuck6475]

"Areas" are new to me so bear with me if I misrepresent this.

Yes when I run you code it does display the desired ranges.

However, the problem is still the same.

Sub Shotgun()

' Builds the tee box array for a shotgun start in the TeeTimes page
' Reverse Shotgun starting with hole 1 and working backwards
' Par 4's & Par 5's have two groups - Par 3's have one group

Dim r1, r2, CoursePar As Range
Dim RevShot As Variant
Dim Cpar As Variant
Dim par As Variant

    With Sheets("Course")
        Set r1 = .Range("e6:m6")    'holes 1 to 9
        Set r2 = .Range("o6:w6")    'holes 10 to 18
        Set CoursePar = Application.Union(r1, r2)
        Cpar = .Range(r1, r2).Value
'       par = .Range(CoursePar).Value
    End With
    
'Set up course par as 2 to 18, 1  - So the loop can go backwards
For i = 1 To CoursePar.Areas.Count
MsgBox "Area " & i & " is " & CoursePar.Areas(i).Address
Next i

    
    RevShot = Array(1, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2)
    
    j = 1       ' index on group
    i = 0       ' index on hole
    c = 4       ' index for cell row for tee group
    
   With Sheets("Tee_Times").Range("b4")
   
    For Each shot In RevShot
    
         If Cpar(1, shot) > 3 Then
            .Cells(j) = shot & "A"
            .Cells(j + 1) = shot & "B"
            j = j + 2
         Else
            .Cells(j) = shot & "A"
            j = j + 1
            End If
            
         Next shot
            
    End With
    
End Sub

Now my Oldtimer logic goes like this. I have two 9 hole ranges combined via UNION into 1 range SET to CoursePar. There shouldn't be 19 or 9 or 55 items in the range. It should be 18.

Now I run your program and it acknowledges the range AREAS (new to me, I have to read up on it) are correct. Do I actually have to modify my use of CoursePar with .Areas(i) to step thru all of the range items?

Understanding that this is a Range and not an Array, I used MS suggested method to move the range to an Array. Namely Cpar = range().value

But alais it doesn't work if I use the UNION develop range i.e. CoursePar as the range identifier (1004 error). It at least runs if I use (r1,r2) for the identifier but it adds cell(n6) to the array for 19 variables not 18, with a undesired variable right in the middle.

Now I can do a work around (spaghetti code here we come) but that isn't the right way to go.

 

[Scott Huish]

I'm thinking that it probably doesn't need to be this complicated. What is it that you are trying to accomplish exactly?

I made some sample data here. What should the results be and why?

Excel Workbook
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
6	1	4	2	5	5	5	3	5	3		1	4	1	3	5	1	2	5	3
Course

 

[Chuck6475]

Hi,

1	2	3	4	5	6	7	8	9	total	10	11	12	13	14	15	16	17	18
4	4	3	4	3	4	5	4	5	36	4	4	3	4	5	4	4	3	5
2	2	1	2	1	2	2	2	2		2	2	1	2	2	2	2	1	2
e	f	g	h	i	j	k	l	m	n	o	p	q	r	s	t	u	v	w

<tbody>
</tbody>

Okay here is what we have.

The first row is a header, second row is the par on the hole with a total of par in the 10th cell. The third row is the number of teams allotted to a particular hole if they all start at the same time (shotgun start).

I added the fourth row to show you what column they are in. Par is on Row 6. Rows 3 and 4 are not actually developed on the spreadsheet they are here only for information.

I was trying to build an array of the Par values less the Total value.

 

[shg]

Why not just use a formula to calculate starting teams per hole? =if(par=3, 1, 2)

Whan you assign a multi-area range to an Variant, you get a 2D array containing the values of the first area in the range. You could create two arrays, Front9 and Back9.

I think that, like some others, I don't understand your broader objective.

 

[Scott Huish]

That doesn't match up with your code:

For Each shot In RevShot

If Cpar(1, shot) > 3 Then
.Cells(j) = shot & "A"
.Cells(j + 1) = shot & "B"
j = j + 2
Else
.Cells(j) = shot & "A"
j = j + 1
End If

Next shot

Where's the As and Bs?

Also, another formula for your sample:

=1+(E2<>3)

 

[Chuck6475]

That doesn't match up with your code:

For Each shot In RevShot

If Cpar(1, shot) > 3 Then
.Cells(j) = shot & "A"
.Cells(j + 1) = shot & "B"
j = j + 2
Else
.Cells(j) = shot & "A"
j = j + 1
End If

Next shot

Where's the As and Bs?

Also, another formula for your sample:

=1+(E2<>3)

Okay, yes (shg) I can calculate the number of groups on a hole using an "IF" statement.

Yes I could actually build the table I made up to show you what I wanted but I don't want it on the input page to confuse the user. Yes I could put it some place else but I could also build it on the run. This routine only runs once per user at best.

So as a novice to all of this I thought here is a simple operation that I can try some of the stuff I learned along the way, which has shown me I still have a great deal more to learn even for the simple stuff.

My desires and statements and in the prior post do match the code provided.

For each Shot in RevShot (RevShot is the order of holes for the shotgun, 1,18,17, ...etc.)
so if Par on hole 1 is not 3 par then first two teams will end up with 1A and 1B, then if hole 18 is not a 3 par then the next two will get 18A and 18B, etc.

The problem is the Par array isn't propagate correctly.