第二次For循环直到第二次运行我的Sub时才起作用

I have some VBA code that first checks if a column exist and if it is needed to insert some columns. Then it takes values from one cell and splits them into the new columns.

我有一些VBA代码首先检查列是否存在以及是否需要插入一些列。然后它从一个单元格中获取值并将它们分成新列。

My problem is when I run the MACRO it only does the inserting of the columns and then I have to run the macro a second time to get the values inserted. I cannot find a similar post about this problem but I am guessing its has been asked before so if you have a link that would be great also.

我的问题是当我运行MACRO它只插入列然后我必须再次运行宏来插入值。我找不到关于这个问题的类似帖子,但我猜它之前已被问过,所以如果你有一个很好的链接。

Is there a reason why my 2nd for loop is not running after the first one finesses?

有没有理由说我的第二个for循环在第一个企业之后没有运行?

Is there a way to tell my code to continue after one For loop ends?

有没有办法告诉我的代码在一个For循环结束后继续?

Dim starting_string As String
Dim primary_code As String
Dim primary_group As String
Dim rng1 As Range
Dim rng2 As Range
Dim space_ndex As Integer
Dim working_string As String

Sub controller()
    Set rng1 = Range("G1:G1")
    Set rng2 = Range("G2:G1429")

    For Each cell In rng1
        If cell.Value <> "Code" Then
            Range("G:H").EntireColumn.Insert
            Cells(1, "G").NumberFormat = "@"
            Cells(1, "H").NumberFormat = "@"
            Cells(1, "G").Value = "Code"
            Cells(1, "H").Value = "Primary Group"
        End If
    Next cell

    For Each cellg In rng2
        working_string = cellg.Offset(0, -1).Value
        If Trim(working_string) <> "" Then
            space_ndex = InStr(cellg.Offset(, -1).Value, " ")
            cellg.Offset(0, 0).NumberFormat = "@"
            cellg.Offset(0, 1).NumberFormat = "@"
            cellg.Offset(0, 0).Value = Left(working_string, space_ndex)
            cellg.Offset(0, 1).Value = Mid(working_string, space_ndex, 100)
        End If
    Next cellg

End Sub

1 个解决方案

#1

Well, you're setting rng2 to "G" before inserting new columns in "G", hence your rng2 is then actually column "I". On second pass it gets re-set to "G".

好吧,你在“G”中插入新列之前将rng2设置为“G”,因此你的rng2实际上是“I”列。在第二遍时,它被重新设置为“G”。

Edit: Try using break points and F8 to find out what your code is doing, it helps ;)

编辑:尝试使用断点和F8来找出你的代码在做什么,它有帮助;)

#1