Macro for checking if a value exists in a different workbook and adding it if it is missing

[Sushajith]

Hi there. I would like to have a macro designed. I have 2 workbooks, one having latest information and the other having the information in database already. both workbooks have 26 columns each. I would like to check if a value that exists in Column A of the "latest-information" workbook also exists in the "already-in-database" workbook. If so, I would like to update all 26 columns of that row in the "already-in-database" workbook with information from the "latest-information" workbook. If the value does not exist, I would like to add all 26 fields in that row into a new sheet on the "already-in-database" workbook. Please help me with this query!

 

[sijpie]

Yes the code above searches for existing values to update them and if not found then add new data at the bottom. if you want only to add all new data to the bottom and not overwrite then the replace this piece of code

   Do While rNew.Value <> vbNullString
        'go through each row until empty row
        Set rOutp = wbDB.ActiveSheet.Columns("A").Find(rNew.Value, _
                        searchdirection:=xlNext)
        If rOutp Is Nothing Then        ' not found, so add
            ' find bottom row
            Set rOutp = wbDB.ActiveSheet.Columns("A").Find(what:="*", _
                        after:=Cells(1, 1), _
                        searchdirection:=xlPrevious).Offset(1, 0)
        End If
        ' set values to row of new data
        rOutp.Resize(1, rNew.CurrentRegion.Columns.Count).Value = _
                rNew.Resize(1, rNew.CurrentRegion.Columns.Count).Value
        
        Set rNew = rNew.Offset(1, 0) ' one row down
    Loop

with

   Do While rNew.Value <> vbNullString
        ' find bottom row
        Set rOutp = wbDB.ActiveSheet.Columns("A").Find(what:="*", _
                        after:=Cells(1, 1), _
                        searchdirection:=xlPrevious).Offset(1, 0)
        ' set values to row of new data
        rOutp.Resize(1, rNew.CurrentRegion.Columns.Count).Value = _
                rNew.Resize(1, rNew.CurrentRegion.Columns.Count).Value
        
        Set rNew = rNew.Offset(1, 0) ' one row down
    Loop

 

[floddy]

Yes the code above searches for existing values to update them and if not found then add new data at the bottom. if you want only to add all new data to the bottom and not overwrite then the replace this piece of code

And if I need to add only missing entries, without updating existing one?

Thanks sijpie.

 

[sijpie]

   Do While rNew.Value <> vbNullString
        'go through each row until empty row
        Set rOutp = wbDB.ActiveSheet.Columns("A").Find(rNew.Value, _
                        searchdirection:=xlNext)
        If rOutp Is Nothing Then        ' not found, so add
            ' find bottom row
            Set rOutp = wbDB.ActiveSheet.Columns("A").Find(what:="*", _
                        after:=Cells(1, 1), _
                        searchdirection:=xlPrevious).Offset(1, 0)
            ' set values to row of new data
            rOutp.Resize(1, rNew.CurrentRegion.Columns.Count).Value = _
                rNew.Resize(1, rNew.CurrentRegion.Columns.Count).Value
        End If


        
        Set rNew = rNew.Offset(1, 0) ' one row down
    Loop

 

[floddy]

Awsome, works great.
Thank you so much.