In Excel2007, Microsoft introduced a "feature" in which, if the CPU Processing
exceeds some level, the Windows "priority" is reduced.presumably to allow the
user to continue working without Excel grabbing all of the CPU cycles.
What that DOES though, is make Excel use a reduced number of CPU cycles.
Secondly, each time you add a VLookup() function, Excel refreshes all PREVIOUS
functions on the worksheet.That means that when you add the VLookup() function
to row 10,001, it evaluates row 10,001 and the previous 10,000 rows!Which also
means that by the time you put the 10,001st value, the first row was evaluated
10,001 times!
What I've done in the past, is utilize a Dictionary object (you can look it up
in VBA help).Using this Dictionary, I loop through the Source sheet store the
key value and lookup value.In your example, you're looking up the value in
column A, and returning the value from B.
So, I'd look through the source file and store the data in the dictionary:
For nRow = 2 To SourceLastRow
If (Not Dict_Source.exists(sourceSheet.Cells(nRow, "A").Value)) Then
Dict_Source.Add sourceSheet.Cells(nRow, "A").Value,
sourceSheet.Cells(nRow, "B").Value
End If
Next nRow
Then, I'd look through the output file and retrieve the matching data from
the Dictionary:
With outputSheet
For nRow = 2 To OutputLastRow
If (Dict_Source.exists(.Cells(nRow, "A").Value)) Then
.Cells(nRow, "B").Value = Dict_Source.Item(.Cells(nRow,
"A").Value)
End If
Next nRow
End With
I've done this with a worksheet containing 160,000 rows and 72 columns.Storing
values into 34 separate dictionaries, then use these Dictionary objects to
generate a summary report.
The summary report takes about 90 seconds to process all 160,000 records.
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
-----------------------------------------
On Thursday, May 5, 2016 4:18 AM, karthik kumar <karthikumar2...@gmail.com>
wrote:
Dear All,
I need to reconcile the two sheets having more than 1,50,000 rows. I used
following VBA code. however it is taking more than 30 minutes to give output.
Sometime it gets stuck when macro running.
Sub MakeFormulas()Dim SourceLastRow As LongDim OutputLastRow As LongDim
sourceSheet As WorksheetDim outputSheet As Worksheet
'What are the names of our worksheets?Set sourceSheet = Worksheets("Sheet1")Set
outputSheet = Worksheets("Sheet2")
'Determine last row of sourceWith sourceSheet SourceLastRow =
.Cells(.Rows.Count, "A").End(xlUp).rowEnd WithWith outputSheet 'Determine
last row in col P OutputLastRow = .Cells(.Rows.Count, "A").End(xlUp).row
'Apply our formula .Range("B2:B" & OutputLastRow).Formula = _
"=VLOOKUP(A2,'" & sourceSheet.Name & "'!$A$2:$B$" & SourceLastRow & ",2,0)"
.Range("B2:B" & OutputLastRow).Copy .Range("B2").PasteSpecial xlPasteValues
Application.CutCopyMode = FalseEnd WithEnd Sub
It will be so helpful if you provide your inputs to cherish the above code.
Please let me know if you need further details.
Thank you,
Regards, --
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post 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 unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
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) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post 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 unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at https://groups.google.com/group/excel-macros.
For more options, visit https://groups.google.com/d/optout.
