仅当首先缺少id到另一个时，才将行从一个工作表复制到另一个工作表

I am new to VBA so I need to copy rows from one sheet into another only if value is missing in first. The cells format stay same always. For now I have this code:

我是VBA的新手，因此我需要将行中的行从一张表复制到另一张表中，只有在第一页中缺少值时。单元格格式总是保持不变。现在我有这个代码：

    Sub test()
    Dim tohere            As Worksheet
    Dim fromhere          As Worksheet
    Dim rngTohere         As Range
    Dim rngfromHere       As Range
    Dim rngCelTohere      As Range
    Dim count             As Integer
    Dim strArray          As Variant
    Dim i                 As Integer

    'Set Workbooks
    Set tohere = ThisWorkbook.Worksheets("Test") 
    Set fromhere = ThisWorkbook.Worksheets("Test 2")

    'Set Columns
    Set rngTohere = tohere.Columns("C") 'this is our column of interest
    Set rngfromHere = fromhere.Columns("C")
    i = 1 'this is counter to foryou to know which row you need to copy

    count = rngfromHere.Cells.Count - WorksheetFunction.CountBlank(rngfromHere)
    strArray = rngfromHere(Cells(1, 1), Cells(count, 1)).Value

    'Loop through each cell in Column C
    For Each rngCelTohere In rngTohere.Cells
        If IsInArray(rngCelTohere.Value, strArray) = False Then
            'here need to put copy entire row into sheet
            'use i row to copy into tohere Worksheet
        End If
        i = i + 1 '
    Next rngCelTohere


End Sub

Function IsInArray(stringToBeFound As Integer, arr As Variant) As Boolean 'this functions returns true or false. for our case we need it to be false
  IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
End Function

Can anyone help me and say if this is good idea, and also help me with copy entire row at the end of sheet. Thanks in advance!

任何人都可以帮助我，并说如果这是个好主意，还可以帮我复制整张表格中的整行。提前致谢！

1 个解决方案

#1

Your code will not compile because of this:

您的代码将无法编译，因为：

Set i = 1 'this is counter to foryou to know which row you need to copy
set count = rngfromHere.Cells.Count - WorksheetFunction.CountBlank(rngfromHere)

These are integers, they are not set. Once you are ready with your code, go to Debug>Compile and check for compiling errors. The VB Editor will show you where they are.

这些是整数，它们没有设置。准备好代码后，转到Debug> Compile并检查编译错误。 VB编辑器将显示它们的位置。

What does the keyword Set actually do in VBA?

关键字Set在VBA中实际做了什么？

Concerning copying the i-th row, see here:

关于复制第i行，请看这里：

Copy row values after another row values, copy whole row to another sheet afterwards

在另一行值之后复制行值，然后将整行复制到另一个工作表

Copy and Paste row by index number in Excel Macro

在Excel宏中按索引编号复制和粘贴行

In your code, your index could be rngCelTohere.Row.

在您的代码中，您的索引可能是rngCelTohere.Row。

#1