嵌套用于下一个循环:外循环不迭代 [英] Nested For Next Loops: Outer loop not iterating

问题描述

我有一个来自A2:A34的数据范围，其中有多个名称，我需要将其复制到范围E9:E14.我只需要复制和粘贴唯一的名称(我不需要相同名称的双精度名称).我很确定使用嵌套For Next循环是解决问题的方法，但是我很难让外部循环进入下一次迭代.现在，这只是给我一个在E9:14中重复的A2:A34范围内的姓氏.我当时正在研究使用Exit For，但是当我在代码中添加它时，外部循环便会迭代，但随后内部循环从2开始.

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

推荐答案

对于该确切问题，我不确定是否真的需要VBA，但希望下面的代码会有所帮助.我切换了循环，以使您仅对大型名称列表进行一次迭代，然后对第二个列表进行迭代以检查重复项.我还添加了一个变量，因此它允许使用5个以上的唯一名称(与tablecount为9到13时不同).

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

这篇关于嵌套用于下一个循环:外循环不迭代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！