Copy row from one sheet to another only if id is missing in first into another

Question

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:

    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!

Vityata · Accepted Answer · 2017-10-24T08:25:25.780

0

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.

What does the keyword Set actually do in VBA?

Concerning copying the i-th row, see here:

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

Copy and Paste row by index number in Excel Macro

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

edited Oct 24 '17 at 08:25

answered Oct 24 '17 at 08:17

Vityata

42,633
8
55
100

I change it, and I will try to compile my code. Thanks for answer, but I still missing part for copy i-th row in my code in loop. I try with this idea cause I'm not using VBA never, and I need it now, I don't know if there is easier way to do this. – Oct 24 '17 at 08:20

Copy row from one sheet to another only if id is missing in first into another

1 Answers1