Nested For Next loop with Vlookup

[djv17]

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

 

[Andrew Poulsom]

What happened to Class 1?

 

[djv17]

no entries in my test data yet.....

 

[djv17]

I found a work around for now....... I added this in the nested loop before +1 to the row counter.

If a = numarena Then
Exit For
End If

It feels really backwards to do this, perhaps there is a cleaner solution.