Consulting

Results 1 to 5 of 5

Thread: looking help

  1. #1
    VBAX Contributor
    Joined
    Jun 2008
    Posts
    169
    Location

    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 ??
    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

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,203
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Contributor
    Joined
    Jun 2008
    Posts
    169
    Location
    Quote Originally Posted by SamT View Post
    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"

  4. #4
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,910
    Location
    Please state in words what you want. A short example file helps us help you better.

    Please use Code tags for code.

  5. #5
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,203
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •