Sorry,
I actually wrote this last week, but evidently never posted it!
I noticed that your code example and your sample file and description is
different.
you SAID that if you find a match, you want to replace B with F and D with H
but in your code:
Cells(r1, "f") = Cells(r, "b")
Cells(r1, "h") = Cells(r, "d")
Does the opposite.
It replaces F with B and H with D.
Not sure which you actually want, but...
I executed your code on 10,000 rows, and it took 26 minutes. (on my Window7
machine, with Excel 2010).
I wrote the following code using a Dictionary Object, and it didn't register
any
time for 10,000 rows.
So I ran it against 100,000 rows, and it took 6 seconds.
The premise is that I create a "Dictionary Object.
I then read the columns E-H and use the E and G columns to create an "index"
for
the Dictionary
and store the F and H columns as the "value" of the Dictionary entry.
I'm only storing the FIRST E&G combination in the dictionary.
If you find a second match and want to overwrite it, then we'll have to modify
the code.
I then read through columns A-D and use A&C and check for a matching Dictionary
entry.
hope this helps,
Paul
-----------------------------------------------------
Option Explicit
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
'----------------------------------------------------
' Read Data and store FIRST record in Dictionary.
'----------------------------------------------------
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
'----------------------------------------------------
' Read Data and update from Dictionary value
'----------------------------------------------------
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 17, 2012 3:27:41 AM
Subject: FW: FW: $$Excel-Macros$$ MATCHING THE VALUE OF COLUMN A AND C WITH
COLUMN E AND G AND UPDATE THE VALUES.
From:Siraj Momin (BTG)
Sent: Monday, September 17, 2012 7:23 AM
To: 'excel-macros@googlegroups.com'
Subject: RE: FW: $$Excel-Macros$$ MATCHING THE VALUE OF COLUMN A AND C WITH
COLUMN E AND G AND UPDATE THE VALUES.
Hi prince
May be I did not explain you properly see the attachment for requirement
Siraj
From:excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] On
Behalf Of Prince Dubey
Sent: Sunday, September 16, 2012 8:47 PM
To: excel-macros@googlegroups.com
Cc: Prince Dubey; Siraj Momin (BTG)
Subject: Re: FW: $$Excel-Macros$$ MATCHING THE VALUE OF COLUMN A AND C WITH
COLUMN E AND G AND UPDATE THE VALUES.
Hi Siraj,
please see the attachment
Regards
Prince
On Sunday, September 16, 2012 6:55:18 PM UTC+5:30, Siraj Momin (BTG) wrote:
Hi Prince
Thank you for the reply, I am sending the attachment on your personal email id
also, I reduced the data but still it taking time I have more data than this to
update, I am not familiar with array formulas that is the problem
Can you help me out in this
Thanks
Siraj
From:Prince Dubey [mailto:prince...@gmail.com]
Sent: Sunday, September 16, 2012 3:42 PM
To: excel-...@googlegroups.com
Cc: Siraj Momin (BTG)
Subject: Re: FW: $$Excel-Macros$$ MATCHING THE VALUE OF COLUMN A AND C WITH
COLUMN E AND G AND UPDATE THE VALUES.
Hi Siraj,
As i saw u r using loop on the rang which runs fine on small amount of data but
in case of large amount of data it will take huge time to process so do one
thing instead of looping on the range get all data in an Array then apply same
logic as u r doing one the range.
Or
Please share ur workbook with us.
regards
Prince
On Sunday, September 16, 2012 3:10:57 PM UTC+5:30, Siraj Momin (BTG) wrote:
ANY SOLUTION POSSIBLE
SIRAJ
From:excel-...@googlegroups.com [mailto:excel-...@googlegroups.com] On Behalf
Of
Siraj Momin (BTG)
Sent: Monday, September 10, 2012 5:41 PM
To: excel-...@googlegroups.com
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-...@googlegroups.com.
To unsubscribe from this group, send email to excel-macros...@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.
--
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.
