首页 文章

VBA代码用于复制特定列中的单元格，并根据条件将其粘贴（值）到另一个工作表上特定列中的单元格

提问于
浏览 次
0

VBA noob在这里，我需要从Sheet1中的特定列复制并根据2个条件将其粘贴到Sheet2中的特定列 . 细节（不是代码）：

i=2 & j=4

IF Sheet1.Cells(i, 4)<>0 and Sheet1.Cells(i, 49)<>0, Type "FK" in _
Sheet2.Cells(j, 1), Type "OF" in Sheet2.Cells(j + 1, 1), copy the following

Copy from **Sheet1**      Paste in **Sheet2**
Cells(i, 52)              Cells(j, 2)
Cells(i, 51)              Cells(j, 3)
Cells(i, 4)               Cells(j, 4)
Cells(i, 1)               Cells(j, 5)
Cells(i, 53)              Cells(j, 6)
Cells(i, 7)               Cells(j, 7)
Cells(i, 10)              Cells(j, 8)
Cells(i, 5)               Cells(j, 9)
Cells(i, 6)               Cells(j, 10)
Cells(i, 49)              Cells(j, 11)
Cells(i, 50)              Cells(j, 12)
Cells(i, 51)              Cells(j, 13)
Cells(i, 51)              Cells(j, 15)
Cells(i, 51)              Cells(j, 16)
Cells(i, 51)              Cells(j, 17)
Cells(i, 19)              Cells(j + 1, 2)
Cells(i, 20)              Cells(j + 1, 3)
Cells(i, 44)              Cells(j + 1, 4)
Cells(i, 28)              Cells(j + 1, 5)
Cells(i, 30)              Cells(j + 1, 6)
Cells(i, 37)              Cells(j + 1, 7)
Cells(i, 41)              Cells(j + 1, 8)
Cells(i, 46)              Cells(j + 1, 9)
Cells(i, 51)              Cells(j + 1, 10)
Cells(i, 51)              Cells(j + 1, 11)

next i

IF Sheet1.Cells(i, 4)<>0 and Sheet1.Cells(i, 49)=0, Type "OF" in _
Sheet2.Cells(j, 1), copy the following

Copy from **Sheet1**    Paste in **Sheet2**
Cells(i, 19)            Cells(j, 2)
Cells(i, 20)            Cells(j, 3)
Cells(i, 44)            Cells(j, 4)
Cells(i, 28)            Cells(j, 5)
Cells(i, 30)            Cells(j, 6)
Cells(i, 37)            Cells(j, 7)
Cells(i, 41)            Cells(j, 8)
Cells(i, 46)            Cells(j, 9)
Cells(i, 51)            Cells(j, 10)
Cells(i, 51)            Cells(j, 11)

next i

Else, next i (start again for i + 1)

所以，基本上我要做的是，如果条件1为真，我将从1行复制数据并将其粘贴到另一张表中的下两个空行中 . 同时，如果条件2为真，则从1行复制数据并将其粘贴到另一个工作表中的下一个空行 . 对不起，很长的帖子，不知道任何其他方式来解释它 . 谢谢大家以前 .

excel vba excel-vba

1 回答

  • 0

    这样做你想要的：

    Sub copyABunchOfThings()

    Dim i As Integer, j As Integer
    i = 2
    j = 4

    For i = 1 To 10 'Change 1 and 10 into your numbers.

    If Sheet1.Cells(i, 4) <> 0 And Sheet1.Cells(i, 49) <> 0 Then
        Sheet2.Cells(j, 1).Value = "FK"
        Sheet2.Cells(j + 1, 1).Value = "OF"

        'Put in **Sheet 2**   what is in **Sheet 1**
        Sheet2.Cells(j, 2) = Sheet1.Cells(i, 52)
        Sheet2.Cells(j, 3) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j, 4) = Sheet1.Cells(i, 4)
        Sheet2.Cells(j, 5) = Sheet1.Cells(i, 1)
        Sheet2.Cells(j, 6) = Sheet1.Cells(i, 53)
        Sheet2.Cells(j, 7) = Sheet1.Cells(i, 7)
        Sheet2.Cells(j, 8) = Sheet1.Cells(i, 10)
        Sheet2.Cells(j, 9) = Sheet1.Cells(i, 5)
        Sheet2.Cells(j, 10) = Sheet1.Cells(i, 6)
        Sheet2.Cells(j, 11) = Sheet1.Cells(i, 49)
        Sheet2.Cells(j, 12) = Sheet1.Cells(i, 50)
        Sheet2.Cells(j, 13) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j, 15) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j, 16) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j, 17) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j + 1, 2) = Sheet1.Cells(i, 19)
        Sheet2.Cells(j + 1, 3) = Sheet1.Cells(i, 20)
        Sheet2.Cells(j + 1, 4) = Sheet1.Cells(i, 44)
        Sheet2.Cells(j + 1, 5) = Sheet1.Cells(i, 28)
        Sheet2.Cells(j + 1, 6) = Sheet1.Cells(i, 30)
        Sheet2.Cells(j + 1, 7) = Sheet1.Cells(i, 37)
        Sheet2.Cells(j + 1, 8) = Sheet1.Cells(i, 41)
        Sheet2.Cells(j + 1, 9) = Sheet1.Cells(i, 46)
        Sheet2.Cells(j + 1, 10) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j + 1, 11) = Sheet1.Cells(i, 51)

    ElseIf Sheet1.Cells(i, 4) <> 0 And Sheet1.Cells(i, 49) = 0 Then
        Sheet2.Cells(j, 1) = "OF"

        'Put in **Sheet 2**   what is in **Sheet 1**
        Sheet2.Cells(j, 2) = Sheet1.Cells(i, 19)
        Sheet2.Cells(j, 3) = Sheet1.Cells(i, 20)
        Sheet2.Cells(j, 4) = Sheet1.Cells(i, 44)
        Sheet2.Cells(j, 5) = Sheet1.Cells(i, 28)
        Sheet2.Cells(j, 6) = Sheet1.Cells(i, 30)
        Sheet2.Cells(j, 7) = Sheet1.Cells(i, 37)
        Sheet2.Cells(j, 8) = Sheet1.Cells(i, 41)
        Sheet2.Cells(j, 9) = Sheet1.Cells(i, 46)
        Sheet2.Cells(j, 10) = Sheet1.Cells(i, 51)
        Sheet2.Cells(j, 11) = Sheet1.Cells(i, 51)
    End If

    Next i

    End Sub
    回复于

相关问题