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?

 

[Scott Huish]

Your sample doesn't show any As or Bs it shows 1s and 2s. Why don't you just loop backward through the range? Why do you need an array at all? What I am asking for is based on the sample data I gave which is in the ranges you specified, what results do you get and why, should there be 36 results (A and B) or what?

 

[Chuck6475]

Thank you for persevering with me.

Most Shotgun are what we call "reverse shotguns". You put the desire number of teams on hole 1 then you fill the course backwards, 18, 17, 16 etc. depending upon the length of the hole. Par is used to differentiate the holes. Par 3's get 1 group and the others typically get 2 groups.

Now for your array.

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

<tbody>
[TD="bgcolor: #cacaca, align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]

</tbody>

In your data, hole 1 would be a par 1 (not possible but it doesn't matter) it is less than 4 so 1 group would be assigned to it. Therefore the first group in the tournament pairings would be assigned to 1A.

On to the next hole which in a "reverse shotgun" is hole 18 which is a par 3 and therefore would also have 1 group. Therefore the second pairing in the tournament would be assigned to 18A.

The next hole is 17 a par 5 which would get 2 groups, therefore group 3 would be 17A and group 4 would be 17B, etc.

I guess I have two issues, yes I want to get this running properly is one of the issues but the second is to understand what is happening with the UNION or GROUPING of ranges that is causing an unwanted cell, namely "N" to be in the data.

 

[Scott Huish]

I think this will do what you want. I wasn't sure if 4 would count as 2 groups or not, if that is the case change this line If c > 4 Then .Offset(1) = n & "B" to If c > 3 Then .Offset(1) = n & "B"

Sub test()
Dim r As Long, c As Range, n As Long
Sheets("Tee_Times").Range("B4") = "1A"
If Sheets("Course").Range("E6") > 4 Then Sheets("Tee_Times").Range("B5") = "1B"
For Each c In Union(Sheets("Course").Range("F6:M6"), Sheets("Course").Range("O6:W6"))
    r = Sheets("Tee_Times").Range("B" & Rows.Count).End(xlUp).Row + 1
    With Sheets("Tee_Times").Cells(r, 2)
        n = 24 - c.Column
        If n < 11 Then n = n + 1
        .Value = n & "A"
        If c > 4 Then .Offset(1) = n & "B"
    End With
Next
End Sub

 

[Chuck6475]

Happy New Year,

I'm not sure what happened but I posted a reply to this tread on the 31st and it isn't here.

I very much appreciate the help I've gotten and don't want you to think I don't.

HotPepper - Wow - There is no way I would have ever done this, this way. Clever! Some tweeking will be necessary as this routine actually uses the 1st hole, then 2nd hole..... verses 1st, 18th, 17th ........ but I can handle that. Thanks.

Q. The "For each" loop uses the UNION method and it appears to work fine. I have to go back and test my "for each" loop where I first SET a variable with a UNION range and then did the loop to see why it didn't work. Any ideas?

Q. Does Excel calculate .end(xlup) each time or is it a stored value. Probably put badly, but speedwise is it faster to do .end or to have a J=J+1, etc. loop to keep track of the group the program is on?