Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Incorporate VLookup into VBA (2002)

    I am running the attached vba script parallel with this formula in column M.

    =IF(L135="","",VLOOKUP(MID(L135,1,6),Bins,2,FALSE) )

    Would it be possible to have the value of the vlookup output with the vba so I can lose the formula (on more than 25000 lines) which should hopefully speed up the worksheet. Thanks
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Incorporate VLookup into VBA (2002)

    You could use something like this:
    <code>
    Dim lngMinL As Long
    Dim lngMaxL As Long
    lngMinL = ?
    lngMaxL = ?
    For lngRow = lngMinL To lngMaxL
    If Range("L" & lngRow) = "" Then
    Range("M" & lngRow) = ""
    Else
    Range("M" & lngRow) = Application.VLookup(Left(Range("L" & lngRow), 6), _
    Range("Bins"), 2, False)
    End If
    Next lngRow
    </code>
    I don't know what the ? should be without more information or seeing the worksheet.

  3. #3
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incorporate VLookup into VBA (2002)

    Thanks Hans, was able to figure the ?'s out. Is there a way of supressing the #N/A?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Incorporate VLookup into VBA (2002)

    You could change
    <code>
    Range("M" & lngRow) = Application.VLookup(Left(Range("L" & lngRow), 6), _
    Range("Bins"), 2, False)
    </code>
    to
    <code>
    Dim varLookup As Variant
    varLookup = Application.VLookup(Left(Range("L" & lngRow), 6), _
    Range("Bins"), 2, False)
    If TypeName(varLookup) = "Error" Then
    Range("M & lngRow) = ""
    Else
    Range("M" & lngRow) = varLookup
    End If</code>

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Incorporate VLookup into VBA (2002)

    Perfect. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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