Copying and updating columns in Visual Basic Excel
-
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 said:
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 said:
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 said:
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 said:
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 said:
Why use a Scripting Dictionary?
Performance. Its significantly faster than repeatedly iterating over a range.
Mind your dates, grasshopper.
-
@Colin said:
Jeez, man, look how old that post you're replying to is! Forsooth!Why doeth thou useth ye olde Scripting Dictionary?
Performance. Its significantly faster than repeatedly iterating over a range.
