CODEWITHSUNDEEP
vbaexcel
user6140865
user6140865

Reputation:

Compare Cell Data and Copy

I found this great snip of code and I am trying to manipulate it to work for me, but I just can't seem to get it. Unfortunately I haven't been able to get my head around it to fully understand it, which doesn't help. So I turn to you. I need to evaluate a column of cells and look for either similarities or differences. If a cell in sheet 1 column 1 is not the same as any of the cells in sheet 2 column 1, I want to copy the entire row into sheet 1 at the bottom of the used area. I've gotten this to the point where what you see will copy the correct first cell, but I can't manipulate it to copy the entire row. I think because of how the 'With' is structured but I need to try to stay away from doing loops since there is over 30k cells to evaluate.

Going down the road I will also be wanting to look for duplicates using the same method above, and if there is a duplicate, compare the adjacent cells for differences and if there is a difference, move the existing data into a comment and move the new data into the existing cell.

Any and all advice is, as always, very appreciated.

Sub Compare_Function()
    Call Get_Master_Cell_Info
    Application.ScreenUpdating = False
    With Sheets("Update").Range(Cells(4, 1), Cells(Rows.Count, 1).End(xlUp)).Offset(, 1)
        .Formula = "=VLOOKUP(A4,'New Master Data 6.1'!A:A,1,FALSE)"
        .Value = .Value
        .SpecialCells(xlCellTypeConstants, 16).Offset(, -1).Copy Sheets("New Master Data 6.1").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .ClearContents
    End With
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 78

Answers (1)

gtwebb
gtwebb

Reputation: 3011

Quick line by line breakdown

This just takes the entire used range from cells A4 to the last used cell in columnA then offsets it by one column so B4:Bx (x is the last used row in column A)

With Sheets("Update").Range(Cells(4, 1), Cells(Rows.Count, 1).End(xlUp)).Offset(, 1)

This puts the formula in all cells so it looks up A4,A5,A6, etc in master sheet, returns the value in the master sheet or an error if its not found. It then copies the values over so they are hardcoded in

.Formula = "=VLOOKUP(A4,'New Master Data 6.1'!A:A,1,FALSE)"
.Value = .Value

Specialcells looks for constants (all cells) and value 16 which means error cells (ie cells don't exist) offsets by -1 (so column A) and copies to new sheet column A at rows.count+1

.SpecialCells(xlCellTypeConstants, 16).Offset(, -1).Copy Sheets("New Master Data 6.1").Range("A" & Rows.Count).End(xlUp).Offset(1)

You might also want to do this after you .clearcontents so you don't get all the error cells in column B

to fix it just change the copied range to .entirerow so 

.SpecialCells(xlCellTypeConstants, 16).entirerow.Copy Sheets("New Master Data 6.1").Range("A" & Rows.Count).End(xlUp).Offset(1)

You will also copy the errors in column B but with this structure there is no getting around that. Can always erase them from the master sheet after.

Also note this code will overwrite any data you have stored in column B.

One more note this code depends on the sheet update being active, it won't run otherwise since your cells function inside your range needs the worksheet explicitly stated, as does your rows.count. You would be better wrapping the whole thing in 2 withs, one for the sheet and one with the range (using .cells and .rows.counmt)

Upvotes: 1

Related Questions