Nested For Next Loops: Outer loop not iterating

Question

I have a range of data from A2:A34 with various names in it that I need to copy to the range E9:E14. I only need to copy and paste unique names (I don't need a double of the same name). I am pretty sure using a nested For Next loop is the way to go but I'm having trouble getting the outer loop to go to the next iteration. Right now this is only giving me the last name in the in range A2:A34 repeated in E9:14. I was looking into using Exit For but when I added that in the code, the outer loop iterated but then the inner loop started over at 2.

Any help with this would be greatly appreciated. Thanks! Below is my code:

Sub FillTable()
  Dim tableCount As Integer
  Dim rowCount As Integer

  For tableCount = 9 To 13
    If Range("E" & tableCount).Value = "" Then
      For rowCount = 2 To 34  
        If Range("E" & tableCount).Value = Range("A" & rowCount).Value Then

        ElseIf Range("E" & tableCount).Value <> Range("A" & rowCount).Value Then
          Range("E" & tableCount).Value = Range("A" & rowCount).Value
        End If
      Next rowCount
    End If
  Next tableCount
End Sub

Answer 1

I am not sure if VBA is really needed for this exact issue but hopefully the below code will help. I switched the loops so that you only iterate through the large list of names once and then you iterate through the second list checking for duplicates. I also added a variable so it would allow for more than 5 unique names (unlike when tablecount was 9 to 13).

Fair warning - this is a quick and easy solution. It is neither elegant nor optimized.

Sub FillTable()

Dim tableCount As Integer
Dim rowCount As Integer
Dim n As Integer

n = 0
For rowCount = 2 To 34

  For tableCount = 9 To 9 + n
      If Range("E" & tableCount).Value = Range("A" & rowCount).Value Then
        ' name already found, break out of loop
        Exit For
      ElseIf Range("E" & tableCount).Value = "" Then
        Range("E" & tableCount).Value = Range("A" & rowCount).Value
        n = n + 1
      End If
  Next tableCount

Next rowCount

End Sub