Sub GetCCC()
Dim rng As Range
Dim cell As Range
Dim CriteriaRng As Range
Dim calcmode As Long
Dim intK As Long
Dim intJ As Long
Dim LastRow As Long
Dim found As Boolean
Dim intI As Long
Dim bol As Boolean
Dim var As String
Dim varint As Long

intI = 2

'With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
' End With
With Sheets("Data")
Set CriteriaRng = .Range("C2", .Cells(Rows.Count, "A").End(xlUp))
End With

Sheets("CIF").Activate
LastRow = [A65536].End(xlUp).Row
'Loop through the cells in the Criteria range
For Each cell In CriteriaRng


For intK = 2 To LastRow And found <> True
With Sheets("CIF").Cells(intK, "A")
If Not IsError(.Value) Then
var = Sheets("CIF").Cells(intK, "A").Value
varint = Right(var, 7)

If varint = cell.Value Then
Sheets("Data").Cells(intI, "H").Value = Sheets("CIF").Cell(intK,"B").Value
intI = intI + 1
Exit For
End If
End If
End With
DoEvents
Next
DoEvents



Next cell

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With
End Sub





above is the code i wrote.
can some1 help me to simplify this code?
because its really run very slow.
for the sheet("CIF"), there are 19k++ records to loop through, and for the sheet("data")
there are 2k++ records also. which mean the system need run 2K * 19K of records...
>< can any1 help me? i really desperate to solve this prob...

thousand thanks in advance
regards,
kysonMok

Hi

There are some you cad do away with

1. DoEvents - you can remove them.

2. Use Cells.Find to find the text you want rather than looping through the entire range of cells

Cheers
Shasur

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)

tq for reply, but how to use cells?
because the number of records mayb different.
thanks agian..

regards,
kysonMok

Here is a hint

var = Sheets("CIF").Cells(intK, "A").Value
varint = Right(var, 7)
Dim rFnd As Range
Set rFnd = Sheets("CIF").Range("A:A").Find(What:=varint, lookat:=xlPart, LookIn:=xlValues)
If Not rFnd Is Nothing Then

End If

__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)

thanks agian, but rather than using excel to loop through all the records,
i decided to switch to ms access, which is faster, then just import back the file into excel.