Nested For Next loop with Vlookup

djv17

New Member
Joined
May 25, 2014
Messages
3
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
 
Last edited:
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top