PDA

View Full Version : vlookup on an array



jazznaura
10-04-2014, 08:27 AM
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
10-05-2014, 12:33 AM
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
10-05-2014, 08:29 AM
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
10-05-2014, 02:53 PM
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
10-05-2014, 03:00 PM
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. :)