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

Class NumberNumber of Arenas Used
Class 14
Class 2a4
Class 2b4
Class 34
Class 42
Class 52

<tbody>
</tbody>

Entry Sheet

col c
(actual)
col k
(actual)
col c
(what I want)
col k
(what I want)
ArenaClassArenaClass
1Class 2a1Class 2a
1Class 2b1Class 2b
1Class 31Class 3
2Class 32Class 3
3Class 33Class 3
4Class 34Class 3
Class 31Class 3
1Class 41Class 4
2Class 42Class 4
Class 41Class 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.

djv17

New Member
Joined
May 25, 2014
Messages
3
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.
 

Forum statistics

Threads
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.
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
Top