looking help

Dear all i have create a code that like VLOOKUP as below , but it run almost 5x mins ?? do any one can help me let its run fast then the stupid one ??

Code:

---

Sub Comparation()
Dim I As Long,
j As Long,
R As Long,
TR As Long,
LR As Long
TR = Sheets("CONS").Range("B" & Rows.Count).End(xlUp).Row
LR = Sheets("OPAR").Range("A" & Rows.Count).End(xlUp).Row
For I = 2 To LR
For j = 2 To TR
If Sheets("OPAR").Cells(I, 1) = Sheets("CONS").Cells(j, 2) Then
Sheets("OPAR").Cells(I, 25) = Sheets("CONS").Cells(j, 4).Value
GoTo Nrow
End If
Next j
Nrow:
Next I
End Sub

---

Code:

---

Sub Comparation()
  Dim TR As Range, Cel As Range
 
  For Each Cel In Sheets("OPAR").Range("A" & Rows.Count).End(xlUp).Row
    Set TR = Sheets("CONS").Range("B2:" & Range("B" & Rows.Count).End(xlUp)).Address.Find(Cel)
    If Not TR Is Nothing Then Cel.Offset(0, 24) = TR.Offset(0, 2)
  Next Cel
End Sub

---

Quote:

---

Originally Posted by SamT

Code:

---

Sub Comparation()
  Dim TR As Range, Cel As Range
 
  For Each Cel In Sheets("OPAR").Range("A" & Rows.Count).End(xlUp).Row
    Set TR = Sheets("CONS").Range("B2:" & Range("B" & Rows.Count).End(xlUp)).Address.Find(Cel)
    If Not TR Is Nothing Then Cel.Offset(0, 24) = TR.Offset(0, 2)
  Next Cel
End Sub

---

---

Dear Samt
thanks you so much to help the code!
have a tried it was prompted "object required":(

Please state in words what you want. A short example file helps us help you better.

Please use Code tags for code.

If you have "Option Explicit" at the top of every code page AND you use the VBA Menu: Debug >> Compile, it will help you find many errors.

If you place the cursor inside a sub and press F8, VBA will step through the code by one line of code each time you press F8. This will tell you when an error occurs.

If you place the cursor inside a VBA Keyword and press F1, the help page for that Keyword will show.

I think I put a Parenthesis in the wrong place.

Try this

Code:

---

Sub Comparation()
  Dim TR As Range, Cel As Range
 
  For Each Cel In Range(Sheets("OPAR").Range("A" & Rows.Count).End(xlUp).Row)
    Set TR = Sheets("CONS").Range("B2:" & Range("B" & Rows.Count).End(xlUp).Address).Find(Cel)
    If Not TR Is Nothing Then Cel.Offset(0, 24) = TR.Offset(0, 2)
  Next Cel
End Sub

---