PDA

View Full Version : looking help

KK1966
07-28-2013, 11:30 PM
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 ??

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

SamT
07-29-2013, 09:21 AM
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

KK1966
07-31-2013, 06:50 AM
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":(

Kenneth Hobs
07-31-2013, 07:20 AM

Please use Code tags for code.

SamT
07-31-2013, 08:05 AM
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

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