How to apply vlookup formula in vba

**Menaka** · 05-01-2013, 08:47 AM

Hi,

How to apply lookup formula of below in vba

=LOOKUP(L2, {1,2,5,10,30,1000}, {"1-2 days","2-5 days","5-10 days","10-30 days","30 days+"})

Thanks in Advance,
Menaka B

**TMS** · 05-01-2013, 09:13 AM

One way:

Dim vResult
vResult = Application.Evaluate("=LOOKUP(L2, {1, 2, 5, 10, 30, 1000}, {""1-2 days"", ""2-5 days"", ""5-10 days"", ""10-30 days"", ""30 days+""})")

Regards, TMS

**Menaka** · 05-01-2013, 10:40 AM

Hi,

Thanks for ur reply, but its not applying properly, all columns

I want something like this to apply for whole "L" Column

.Range("Z2:Z" & lRow) = Application.Evaluate("=LOOKUP(.Range("$L$2:$L" & lRow), {1, 2, 5, 10, 30, 1000}, {""1-2 days"", ""2-5 days"", ""5-10 days"", ""10-30 days"", ""30 days+""})")

I've attached the sheet and the VBAcode with this, please take a look at it. The expected result of Z column is in sheet2, column D

**TMS** · 05-01-2013, 11:38 AM

OK, do it this way:

Sub Test()
Dim wb1 As Workbook
Dim ws1 As Worksheet
Dim lRow As Long

Set wb1 = ThisWorkbook
Set ws1 = wb1.Worksheets("Sheet1")

With ws1
    lRow = .Range("A" & .Rows.Count).End(xlUp).Row
    With .Range("Z2:Z" & lRow)
       .Formula = "=LOOKUP($L2, {1, 2, 5, 10, 30, 1000}, {""1-2 days"", ""2-5 days"", ""5-10 days"", ""10-30 days"", ""30 days+""})"
       .Value = .Value
    End With
End With

End Sub

Regards, TMS