VBA code to lookup and fill across

[ramananhrm]

Hi,

Im trying to write a VBA code for lookup few words in a range of cells.

There was no success and end up in recording macro and below mentioned is the code (Using record Macro)

Sub normallookupstyle()


Selection.FormulaR1C1 = "=VLOOKUP(RC[-1],{123;""23KC"";""Animal"";""Planet"";""Split"";""Layout""},1,0)"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


End Sub

I have mentioned the un-successful code below (As im not good in VBA coding)

Sub codingstylelookup()
Dim lookrange() As Variant


lookrange(123,""23KC"",""Animal"",""Planet"",""Split"",""Layout"")


ActiveCell.Value = Application.WorksheetFunction.VLookup(ActiveCell(0, -1).Value, lookrange, 1, 0)


End Sub


Please help me out with a correct VBA code for the code i have highlighted in red color

I would like to fill across the range between B2 to B18 (File attached)


https://app.box.com/s/4soit7o7ffxr45wbxl9rycukd08274s4

Excel Forum link:

http://www.excelforum.com/showthread.php?t=1129457&p=4331014#post4331014