First of all,
Your statement of work and you program is backwards.
You SAID:
"... if matched update the value of Col B to Col. F and the value of Col D to
Col H"
but your program says:
Cells(r1, "f") = Cells(r, "b")
Cells(r1, "h") = Cells(r, "d")
which is setting "F" to "B", not "B" to "F"...
just so we're clear as to which one you intend...
Next:
This WOULD work ok with a small data set.
but: let's say you have 1000 rows.
for each of the 1000 rows, you're making 1000 COMPARISONS.
That's 1,000,000 comparisons.. for ONLY 1000 rows.
If your data set was 10,000 rows, that would be 100,000,000 comparisions.
That WOULD take a while.
in your comparisons though, once you find a match, you should stop looking.
add a "Exit For" to:
Cells(r1, "f") = Cells(r, "b")
Cells(r1, "h") = Cells(r, "d")
Exit for
Unless you EXPECT an additional match and you want to keep the later one.
In which case, I would reverse the loop and search from the bottom up:
For r1 = Cells(Rows.Count, "g").End(xlUp).Row to 2 step -1
=======================================================================
Now, if you REALLY want cut out some time, I would:
Load a Data Dictionary object
Then check for the values in the Dictionary and update the values stored there.
=======================================================================
I tested your previous macro on 10,000 rows.
It took 25 minutes.
I tested the following macro on 100,000 rows.
It took 16 seconds....
I know which one *I* would prefer!
==========================================================================
Sub CheckSystem2()
Dim Dict_Data
Dim r As Long
Dim c As Long
Dim nRows, dArray
Dim tstart, tstop, tElapsed, tMin, tSec
tstart = Timer
Set Dict_Data = CreateObject("Scripting.Dictionary")
nRows = Cells(Rows.Count, "a").End(xlUp).Row
For r = 2 To nRows
If (r Mod 100 = 0) Then Application.StatusBar = "Loading Dictionary: "
&
r & " of " & Cells(Rows.Count, "a").End(xlUp).Row
If (Not Dict_Data.exists(Cells(r, "e").Value & "|" & Cells(r,
"g").Value)) Then
Dict_Data.Add Cells(r, "e").Value & "|" & Cells(r, "g").Value,
Cells(r, "b").Value & "|" & Cells(r, "d").Value
End If
Next r
For r = 2 To nRows
If (r Mod 100 = 0) Then
Application.StatusBar = r & " of " & Cells(Rows.Count,
"a").End(xlUp).Row
End If
If (Dict_Data.exists(Cells(r, "a").Value & "|" & Cells(r, "c").Value))
Then
dArray = Split(Dict_Data.Item(Cells(r, "a").Value & "|" & Cells(r,
"c").Value), "|")
Cells(r, "f") = dArray(0)
Cells(r, "h") = dArray(1)
End If
Next r
Application.StatusBar = False
tstop = Timer
tElapsed = tstop - tstart
tMin = tElapsed \ 60
tSec = tElapsed Mod 60
MsgBox "Finished" & Chr(13) & tMin & " min " & tSec & " sec"
End Sub
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
________________________________
From: Siraj Momin (BTG) <smo...@ccc.ae>
To: excel-macros@googlegroups.com
Sent: Mon, September 10, 2012 9:41:53 AM
Subject: $$Excel-Macros$$ MATCHING THE VALUE OF COLUMN A AND C WITH COLUMN E
AND
G AND UPDATE THE VALUES.
Dear members
The following macro I am using to match the values of col A and Col C with Col
E
and Col G and if matched update the value of Col B to Col. F and the value of
Col D to Col H. Its working fine on small data but on large data its taking
time
can anyone suggest some correction or improvement in this macro.
Due to the size limit of the group I did not attached the file.
Siraj
Option Explicit
Sub checksyssystem1()
Dim r As Long
Dim r1 As Long
Dim c As Long
For r = 2 To Cells(Rows.Count, "a").End(xlUp).Row
For r1 = 2 To Cells(Rows.Count, "g").End(xlUp).Row
If Cells(r, "a") = Cells(r1, "e") And Cells(r, "c") = Cells(r1, "g") Then
Cells(r1, "f") = Cells(r, "b")
Cells(r1, "h") = Cells(r, "d")
If Cells(r, "a") = "" Then
Exit For
End If
End If
Next r1
Next r
End Sub--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners
and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
--
Join official facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES (1120+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
6) Jobs posting is not allowed.
7) Sharing copyrighted ebooks/pirated ebooks/their links is not allowed.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To post to this group, send email to excel-macros@googlegroups.com.
To unsubscribe from this group, send email to
excel-macros+unsubscr...@googlegroups.com.
