4

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

Andres
  • 45
  • 4
  • 6
    You can just record a macro and enter your formula then stop the macro. Then you will have your answer – Pierre44 Oct 31 '18 at 14:14
  • 2
    `ThisWorkbook.Sheets("Sheet2").Range("B2").Formula = "=LOOKUP(Sheet1!F2,{" & """" & "FL01" & """" & ",FL12" & """ ",FL21" & """" & ",FL31" & """" & ",FL34" & """" & "},{" & """" & "ORLANDO PICKLIST" & """" & ",TAMPA PICKLIST" & """" & ",JAX PICKLIST" & """" & ",NAPLES PICKLIST" & """" & ",MIAMI PICKLIST" & """" & "})"` – Rahul Chawla Oct 31 '18 at 14:17

1 Answers1

5

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.

Wizhi
  • 6,424
  • 4
  • 25
  • 47
  • 3
    The macro recorder _is_ a great way to figure out to start writing VBA code. Extra +1 for making note of the fact that it writes _terrible_ (but functional) code that then needs to be massaged into something decent. – FreeMan Oct 31 '18 at 14:40
  • 1
    And [here's a good SO thread](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) on how to avoid using `.Select/.Activate`. – BruceWayne Oct 31 '18 at 14:43