VBA Append if it does not exist

[gerald24]

Hi Guys,

Need help with the below. I want to append my initial list from "Final" Sheet but if the same details already exist, I want it to just move to the next.
So it is like, I have a concatenate of Class, Name and Age in one sheet, and I have the same concatenate to another sheet.

My code is really messy, TIA!!!

Sub Append()

Dim LastRow As Integer
Dim LastRow2 As Integer
Dim Class, Name, Age, Multirange As Range
Dim Class2, Name2, Age2, Multirange2 As Range
Dim CL As Range
Dim Rng As Range

Sheets("Sheet1").Select

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Set Class = Range("B2:B" & LastRow)
Set Name = Range("D2:D" & LastRow)
Set Age = Range("F2:F" & LastRow)
Set Multirange = Union(Fund, TID, Forward_Date)

Set Class2 = Sheets("Computation").Range("A2:A" & LastRow)
Set Name2 = Sheets("Computation").Range("B2:B" & LastRow)
Set Age2 = Sheets("Computation").Range("C2:C" & LastRow)
Set Multirange2 = Union(Fund, TID, Forward_Date)


   For Each CL In Multirange
            If Not CL.Value = Multirange2 Then
                If Rng Is Nothing Then
                    Set Rng = CL
                Else
                    Set Rng = Union(Rng, CL)
                End If
            End If
    Next CL
            If Not Rng Is Nothing Then Rng.Copy
            Sheets("Final").Select
            Range("A" & Rows.Count).Select
            ActiveCell.End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues




End Sub

 

[gerald24]

Up

 

[gerald24]

Up

 

[gerald24]

Up. Anyone please

 

[StephenCrump]

I am guessing, because it's not really clear from your description or your code, that you are trying to do something like this?

Book1
	A	B	C
1	Class	Name	Age
2	Maths	Michael	15
3	Maths	Molly	15
4	English	Mandy	14
5	English	Mindy	14
6	English	Mindy	15
7	English	Monty	14
SomeSheetName

BEFORE:

Book1
	A	B	C
1	Class	Name	Age
2	History	Mark	16
3	History	Millie	17
4	Maths	Molly	15
5	English	Mandy	14
6	English	Mindy	15
7	English	Monty	14
OtherSheetName

AFTER (the two records highlighted have been appended)

Book1
	A	B	C
1	Class	Name	Age
2	History	Mark	16
3	History	Millie	17
4	Maths	Molly	15
5	English	Mandy	14
6	English	Mindy	15
7	English	Monty	14
8	Maths	Michael	15
9	English	Mindy	14
OtherSheetName

 

[gerald24]

Yes Sir!!! That's exactly what I need!

 

[StephenCrump]

Yes Sir!!! That's exactly what I need!

OK, code should be quite straightforward, but I can't post back now until tomorrow.

Someone else may be able to help in the meantime, now we've clarified what you're looking for.

 

[StephenCrump]

There will be a few ways to do this. Here is one:

Sub Append()

    Dim FirstRow As Long, LastRow As Long, lCount As Long, i As Long
    Dim rng As Range
    Dim vIn As Variant, vOut() As Variant

    FirstRow = 2
    
    'Assumes Class, Name, Age in cols B,D,F of "Final", and A,B,C of "Computation"
    With Worksheets("Final")
        vIn = .Range("B" & FirstRow & ":F" & .Cells(Rows.Count, 2).End(xlUp).Row).Value2
    End With
    With Worksheets("Computation")
        Set rng = .Range("A" & FirstRow & ":C" & .Cells(Rows.Count, 1).End(xlUp).Row)
    End With
    ReDim vOut(1 To UBound(vIn), 1 To 3)

    For i = 1 To UBound(vIn)
        If Application.CountIfs(rng.Columns(1), vIn(i, 1), rng.Columns(2), vIn(i, 3), rng.Columns(3), vIn(i, 5)) = 0 Then
            lCount = lCount + 1
            vOut(lCount, 1) = vIn(i, 1)
            vOut(lCount, 2) = vIn(i, 3)
            vOut(lCount, 3) = vIn(i, 5)
        End If
    Next i
    
    If lCount > 0 Then rng.Offset(rng.Rows.Count).Resize(lCount, 3).Value = vOut

End Sub

 

[gerald24]

There will be a few ways to do this. Here is one:

Sub Append()

    Dim FirstRow As Long, LastRow As Long, lCount As Long, i As Long
    Dim rng As Range
    Dim vIn As Variant, vOut() As Variant

    FirstRow = 2
    
    'Assumes Class, Name, Age in cols B,D,F of "Final", and A,B,C of "Computation"
    With Worksheets("Final")
        vIn = .Range("B" & FirstRow & ":F" & .Cells(Rows.Count, 2).End(xlUp).Row).Value2
    End With
    With Worksheets("Computation")
        Set rng = .Range("A" & FirstRow & ":C" & .Cells(Rows.Count, 1).End(xlUp).Row)
    End With
    ReDim vOut(1 To UBound(vIn), 1 To 3)

    For i = 1 To UBound(vIn)
        If Application.CountIfs(rng.Columns(1), vIn(i, 1), rng.Columns(2), vIn(i, 3), rng.Columns(3), vIn(i, 5)) = 0 Then
            lCount = lCount + 1
            vOut(lCount, 1) = vIn(i, 1)
            vOut(lCount, 2) = vIn(i, 3)
            vOut(lCount, 3) = vIn(i, 5)
        End If
    Next i
    
    If lCount > 0 Then rng.Offset(rng.Rows.Count).Resize(lCount, 3).Value = vOut

End Sub

Thank you very much for this, Sir! I will try this ASAP as I am freed for I am swamped with work.

Would you be the best Samaritan and care enough to explain how the code works? So that I can learn from this.

Thank you so much again!!!!

 

[gerald24]

There will be a few ways to do this. Here is one:

Sub Append()

    Dim FirstRow As Long, LastRow As Long, lCount As Long, i As Long
    Dim rng As Range
    Dim vIn As Variant, vOut() As Variant

    FirstRow = 2
    
    'Assumes Class, Name, Age in cols B,D,F of "Final", and A,B,C of "Computation"
    With Worksheets("Final")
        vIn = .Range("B" & FirstRow & ":F" & .Cells(Rows.Count, 2).End(xlUp).Row).Value2
    End With
    With Worksheets("Computation")
        Set rng = .Range("A" & FirstRow & ":C" & .Cells(Rows.Count, 1).End(xlUp).Row)
    End With
    ReDim vOut(1 To UBound(vIn), 1 To 3)

    For i = 1 To UBound(vIn)
        If Application.CountIfs(rng.Columns(1), vIn(i, 1), rng.Columns(2), vIn(i, 3), rng.Columns(3), vIn(i, 5)) = 0 Then
            lCount = lCount + 1
            vOut(lCount, 1) = vIn(i, 1)
            vOut(lCount, 2) = vIn(i, 3)
            vOut(lCount, 3) = vIn(i, 5)
        End If
    Next i
    
    If lCount > 0 Then rng.Offset(rng.Rows.Count).Resize(lCount, 3).Value = vOut

End Sub

Tried the above. thought that it did not work because it was soft, but it worked fast and perfect.
Could you please explain the functions you used?

Thanks again!