Excel VBA - =lookup in macro

user_10576027 0
Viewed 133

I'm using this formula to get info from another sheet. I would like to make it in a macro.

=LOOKUP(Sheet1!F2,{"FL01","FL12","FL21","FL31","FL34"},{"ORLANDO PICKLIST","TAMPA PICKLIST","JAX PICKLIST","NAPLES PICKLIST","MIAMI PICKLIST"})

Also the answer to be in sheet2 B2 center font size 24 .

Thank you

vba excel
1 Answers

By recording it

enter image description here

you will get something like this:

Option Explicit

Sub Macro3()
'
' Macro3 Macro
'

'
    Selection.FormulaArray = _
        "=LOOKUP(Sheet1!RC[4],{""FL01"";""FL12"";""FL21"";""FL31"";""FL34""},{""ORLANDO PICKLIST"";""TAMPA PICKLIST"";""JAX PICKLIST"";""NAPLES PICKLIST"";""MIAMI PICKLIST""})"
    With Selection.Font
        .Name = "Calibri"
        .Size = 24
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
    End With
End Sub

Where selection is which cell I selected when I started recording ("B2"). From here you can play around with the code. You will get a feeling for how Excel "Translate" your formulas and your format options into code.

By edit the selection part (Selection.) we can be more explicit which worksheet and which range we want to apply the new code into... 

Sub Macro3_Changes()
'
' Macro3 Macro
'

'
    Worksheets("Sheet2").Range("B2").FormulaArray = _
        "=LOOKUP(Sheet1!RC[4],{""FL01"";""FL12"";""FL21"";""FL31"";""FL34""},{""ORLANDO PICKLIST"";""TAMPA PICKLIST"";""JAX PICKLIST"";""NAPLES PICKLIST"";""MIAMI PICKLIST""})"
    With Selection.Font
        .Name = "Calibri"
        .Size = 24
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
    End With
End Sub

Here is some interesting note, I assume that you had an Array formula. I enter your formula with Ctrl + Shift + Enter. The macro records that too and translate it as FormulaArray. If I have just enter the formula it would have been FormulaR1C1 which is a relative formula from the section you selected.

user_45525290
Related