Hi, I'm a newbie in using VBA Excel and have never use VBA excel before and am totally lost in my problem. Thus, i would GREATLY appreciate a direction or two.

I have 2 worksheets ONE and TWO. Both have the same columns type and column number (approximately 15) with worksheet TWO being the more updated worksheet. I need to find the rows in worksheet TWO which are not present in worksheet ONE by comparing column A (contains an ID number) in both worksheets. After which i will copy the whole row from worksheet TWO to worksheet ONE. During the process of comparing column A in both worksheets, i also need to update column C and E of worksheet ONE by comparing these 2 columns using column A.

I can do the above without using VBA but i need to create a macro for it to automate the whole process for future uses.

Would appreciate any help greatly! Thanks in advance!

I am encountering a similar problem and was wondering if you have found a solution. Would appreciate it if you could post the solution, if you have one! Thank you very much.

Plz send teh codez.

gxangel:

automate the whole process

 

Simple! Delete worksheet ONE and replace it with worksheet TWO. Now worksheet ONE is up to date.

 You're welcome. My rate is 1500 rupees/hour if you need further assistance.

Nandurius:

My rate is 1500 rupees/hour if you need further assistance.

But Link can only hold 500, and sometimes 999, how is anyone going to be able to pay that?

Nandurius:

You're welcome. My rate is 1500 rupees/hour if you need further assistance.

 

That's pretty cheap, ~36 USD. So I guess you have adjusted your rate to your clients' level?

 

A lot of it will depend on how large the sheets are, whether they are sorted, etc.

A general solution that might fit your needs it to build up an index using a Collection or a Scripting.Dictionary, mapping the key value to the row number or offset. (You may need to add a reference to the Microsoft Scripting Something) Then just iterate over the keys() in one list and see if they exist in the other list

   enum DATA_COLUMNS
      COL_ID
      ....
    end enum

    ' Load Source
    Dim dictSource As Scripting.Dictionary: Set dictSource = CreateObject("Scripting.Dictionary")
    Dim rngSource As Range: Set rngSource = Range("source1")

    Dim i as Long: i = 1
    Do While Not IsEmpty(rngSource.Offset(i, COL_ID))
        unique_id = Trim(rngSource.Offset(i, COL_ID).Value)
        If Not dictSource.exists(unique_id) Then
            dictSource.Add unique_id, i
        Else
            dictSource(unique_id) = i ' assume later version are better
        End If
        i = i + 1
    Loop

 ' ---- and later

    For Each unique_id In dictSource.keys
                    
        If Not dictTarget.exists(unique_id) Then
             ' ---- do something
        Else
             ' ---- do something else
        End If
   Loop

Colin:

A lot of it will depend on how large the sheets are, whether they are sorted, etc.

A general solution that might fit your needs it to build up an index using a Collection or a Scripting.Dictionary, mapping the key value to the row number or offset. (You may need to add a reference to the Microsoft Scripting Something) Then just iterate over the keys() in one list and see if they exist in the other list

...

Why use a Scripting Dictionary? Seems odd to me. My first approach would be to just record an Excel macro doing one row update manually, then add looping and testing code around it to fully automate the process over the required range.

Why use a Scripting Dictionary?

 Performance.  Its significantly faster than repeatedly iterating over a range.

 

Colin:

Why use a Scripting Dictionary?

 Performance.  Its significantly faster than repeatedly iterating over a range.

Mind your dates, grasshopper.

Colin:

Why doeth thou useth ye olde Scripting Dictionary?

 Performance.  Its significantly faster than repeatedly iterating over a range.

Jeez, man, look how old that post you're replying to is! Forsooth!