VBA code to lookup and fill across

[Ramanan Arumugam]

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)

Mr. Excel Question Board link:
http://www.mrexcel.com/forum/excel-q...ll-across.html

Excel Forum link:
http://www.excelforum.com/showthread...14#post4331014

 

[Nebu]

Hi:

Try the following code:

Sub codingstylelookup()

Dim lookrange As Variant
Dim str$
lookrange = Array(123, "23KC", "Animal", "Planet", "Split", "Layout")

For i& = LBound(lookrange) To UBound(lookrange)
    str = Cells(ActiveCell.Row, ActiveCell.Column - 1).Value
    If str = lookrange(i) Then
    ActiveCell.Value = lookrange(i)
    Exit For
    Else
    ActiveCell.Value = "N/A"
    End If
Next

End Sub

Thanks

 

[shrivallabha]

Hi Ramanan,

Why are you using VBA code for which you already have formula? There must be a good reason