Excel VBA-根据单元格值复制和插入行 [英] Excel VBA- copy and insert row based on cell value

问题描述

我正在努力完成这个：

列G ========>新列G

column G ========> new column G

2                            1

                             2

2                            1

                             2

1                            1

1                            1

2                            1

                             2

我已经看了很多不同的问题来回答这个问题，但我相信我的代码是不正确的，因为我最初要复制整个行G = 2并将其直接插入到下面通常将其复制到excel中的另一张表。

I've looked at many different questions to answer this but I believe my code is incorrect because I want to copy the entire row when initially G = 2 and insert it directly beneath, instead of the usual copy it to another sheet in excel.

Sub duplicate()
Dim LastRow As Long
Dim i As Integer
For i = 2 To LastRow
If Range("G" & i).Value = "2" Then
    Range(Cells(Target.Row, "G"), Cells(Target.Row, "G")).Copy
    Range(Cells(Target.Row, "G"), Cells(Target.Row, "G")).EntireRow.Insert    Shift:=xlDown
End If
Next i
End Sub

非常感谢任何和所有的帮助!!

Thank you so much to any and all help!!

Excel VBA自动化 - 复制行x基于单元格值的次数

推荐答案

Public Sub ExpandRecords()

    Dim i As Long, s As String
    Const COL = "G"

    For i = 1 To Cells(Rows.Count, COL).End(xlUp).Row
        If Cells(i, COL) = 2 Then
            s = s & "," & Cells(i, COL).Address
        End If
    Next
    If Len(s) Then
        Range(Mid$(s, 2)).EntireRow.Insert
        For i = 1 To Cells(Rows.Count, COL).End(xlUp).Row
            If Cells(i, COL) = vbNullString Then
                Cells(i, COL).Value = 1
            End If
        Next
    End If

End Sub

这篇关于Excel VBA-根据单元格值复制和插入行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！