# Nested For Next loop with Vlookup

#### djv17

##### New Member
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

 Class Number Number of Arenas Used Class 1 4 Class 2a 4 Class 2b 4 Class 3 4 Class 4 2 Class 5 2

<tbody>
</tbody>

Entry Sheet

 col c (actual) col k (actual) col c (what I want) col k (what I want) Arena Class Arena Class 1 Class 2a 1 Class 2a 1 Class 2b 1 Class 2b 1 Class 3 1 Class 3 2 Class 3 2 Class 3 3 Class 3 3 Class 3 4 Class 3 4 Class 3 Class 3 1 Class 3 1 Class 4 1 Class 4 2 Class 4 2 Class 4 Class 4 1 Class 4

<tbody>
</tbody>

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:

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### Andrew Poulsom

##### MrExcel MVP
What happened to Class 1?

#### djv17

##### New Member
no entries in my test data yet.....

#### djv17

##### New Member
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.

Replies
5
Views
144
Replies
2
Views
64
Replies
5
Views
118
Replies
2
Views
71
Replies
0
Views
298

1,171,794
Messages
5,877,556
Members
433,265
Latest member
Umaratnam

### 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.

### Which adblocker are you using?

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

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