vlookup on an array

[jazznaura]

hi all,

need some help with a vlookup on an array,

below is what i have but can't figure out the vlookup, get a 'Object doesnt support this method or property' error

have used similar in excel when entered into cell but never tried in vba and with an array.

Any help would be great.

thanks

jazz,

OR04array() = Import.Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 7)).Value
FR10Xarray() = Range("A1", Range("A" & Rows.Count).End(xlUp).Offset(, 5)).Value




        For I = LBound(OR04array, 1) To UBound(OR04array, 1)


OR04array(I, 4) = WorksheetFunction.IF(WorksheetFunction.VLookup(OR04array(I, 1), FR10Xarray(), 1, True) = OR04array(I, 1), WorksheetFunction.VLookup(OR04array(I, 1), FR10Xarray(), 3, True), "None") 


        Next I


Range("J1").Resize(UBound(OR04array, 1), UBound(OR04array, 2)).Value = OR04array


Erase OR04array() 
Erase FR10Xarray()

[Bob Phillips]

Try

For I = LBound(OR04array, 1) To UBound(OR04array, 1)
     
    idx = 0
    On Error Resume Next
        idx = Application.Match(OR04array(I, 1), FR10Xarray(), 0)
    On Error GoTo 0
    If idx = 0 Then
    
        OR04array(I, 4) = "None"
    
    Else
     
        OR04array(I, 1) = FR10Xarray(idx)
    End If
next I

[jazznaura]

hi xld,

thank you for your reply and suggestion. sorry i didn't explain myself clearly in my original post.

i am able to use solutions such as the one you suggested but i'm interested in seeing if i can use a non exact match vlookup with an array, as this is the fastest way i know of doing a vlookup in a sheet.

i guess my question really is, can i use the below method with an array ? will it be faster than a exact match vloopup ? , (this is what i originally wanted to test but couldn't get it to work), can i do a binary search on an array?

even if someone can tell me that the below is not possible on an array would be great,

thanks,

WorksheetFunction.IF(WorksheetFunction.VLookup(OR04array(I, 1), FR10Xarray(), 1, True) = OR04array(I, 1), WorksheetFunction.VLookup(OR04array(I, 1), FR10Xarray(), 3, True), "None")
 

[Bob Phillips]

For I = LBound(OR04array, 1) To UBound(OR04array, 1)
     
    If WorksheetFunction.VLookup(OR04array(I, 1), FR10Xarray(), 1, True) = OR04array(I, 1) Then
    
        OR04array(I, 4) = WorksheetFunction.VLookup(OR04array(I, 1), FR10Xarray(), 3, True)
    Else
    
        OR04array(I, 4) = "None"
    End If
Next I

[jazznaura]

hi,

you know after your last suggestion i thought i should break it up, but havent had a chance to look at it today.

thanks again for your help.