I have found this site to be extremely helping in coding my first big excel vba project! Thank you! Using the website I have been able to answer most of my questions except this one! Maybe I am approaching it wrong?!
I have 2 sheets in the same workbook -- Entry and Setup. Setup sheet contains details about each class including how many arenas are going to be used for the event. The entries need to be divided up amongst the arenas. We are dealing with around 500 entries.
Setup Sheet
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]Class Number[/TD]
[TD]Number of Arenas Used[/TD]
[/TR]
[TR]
[TD]Class 1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Class 2a[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Class 2b[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Class 3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Class 4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Class 5[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
Entry Sheet
[TABLE="class: grid, width: 450, align: left"]
<tbody>[TR]
[TD="align: center"]col c
(actual)[/TD]
[TD="align: center"]col k
(actual)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]col c
(what I want)[/TD]
[TD="align: center"]col k
(what I want)[/TD]
[/TR]
[TR]
[TD="align: center"]Arena[/TD]
[TD="align: center"]Class[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Arena[/TD]
[TD="align: center"]Class[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 2a[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 2a[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 2b[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 2b[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 3[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Class 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Class 3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Class 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Class 3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Class 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Class 3[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Class 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 3[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 4[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Class 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Class 4[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Class 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 4[/TD]
[/TR]
</tbody>[/TABLE]
Code
Dim r As Integer
Dim a As Integer
Dim numarena As Integer
Dim c2 As Range
Dim c3 As Range
Dim clslstrow As Long
Dim lstrow As Long
lstrow = wsE.Range("D" & Rows.count).End(xlUp).row
clslstrow = wsCL.Range("A" & Rows.count).End(xlUp).row
Table1 = wsCL.Range("A2:N" & clslstrow)
For r = 2 To lstrow
Set c2 = wsE.Range("K" & r)
Set c3 = wsE.Range("K" & r + 1)
numarena = Application.VLookup(c2, Table1, 5, False)
For a = 1 To numarena
wsE.Cells(r, 3).Value = a
If c2 <> c3 Then
Exit For
End If
r = r + 1
Next a
' IT IS HAPPENING HERE .... when 'a' gets to numarena but have already added r+1
Next r
I have 2 sheets in the same workbook -- Entry and Setup. Setup sheet contains details about each class including how many arenas are going to be used for the event. The entries need to be divided up amongst the arenas. We are dealing with around 500 entries.
Setup Sheet
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]Class Number[/TD]
[TD]Number of Arenas Used[/TD]
[/TR]
[TR]
[TD]Class 1[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Class 2a[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Class 2b[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Class 3[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Class 4[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Class 5[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]
Entry Sheet
[TABLE="class: grid, width: 450, align: left"]
<tbody>[TR]
[TD="align: center"]col c
(actual)[/TD]
[TD="align: center"]col k
(actual)[/TD]
[TD="align: center"][/TD]
[TD="align: center"]col c
(what I want)[/TD]
[TD="align: center"]col k
(what I want)[/TD]
[/TR]
[TR]
[TD="align: center"]Arena[/TD]
[TD="align: center"]Class[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Arena[/TD]
[TD="align: center"]Class[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 2a[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 2a[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 2b[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 2b[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 3[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Class 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Class 3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Class 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]Class 3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Class 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]Class 3[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Class 3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 3[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 4[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Class 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Class 4[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]Class 4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Class 4[/TD]
[/TR]
</tbody>[/TABLE]
Code
Dim r As Integer
Dim a As Integer
Dim numarena As Integer
Dim c2 As Range
Dim c3 As Range
Dim clslstrow As Long
Dim lstrow As Long
lstrow = wsE.Range("D" & Rows.count).End(xlUp).row
clslstrow = wsCL.Range("A" & Rows.count).End(xlUp).row
Table1 = wsCL.Range("A2:N" & clslstrow)
For r = 2 To lstrow
Set c2 = wsE.Range("K" & r)
Set c3 = wsE.Range("K" & r + 1)
numarena = Application.VLookup(c2, Table1, 5, False)
For a = 1 To numarena
wsE.Cells(r, 3).Value = a
If c2 <> c3 Then
Exit For
End If
r = r + 1
Next a
' IT IS HAPPENING HERE .... when 'a' gets to numarena but have already added r+1
Next r
Last edited: