Create Macro with User Input Boxes inside an Array Formula

[crystal_robinson]

I think I kind of have a basic feel of what needs to be done, but am not getting it to work. I need to look up a part number and PO number on tab one, and match it to the part and po columns in the table on tab 2, returning the value pieces shipped, from the table from tab 2. I have been using the formula by typing it manually, and no one else in my office is capable of that. Is there a way to make it a macro?

Formula is
=INDEX(Table1,MATCH(C2092&D2092,Table1[Column1]&Table1[Column2],0),7)

I need a macro
{=INDEX("select table array",MATCH("select part"&"select PO","select parts column"&"select PO column",0)"select column number")}


This is my macro, in which I receive an error at the last part for selecting column number and even if I make that a constant I am getting #NAME error

Sub LookupValuesa()
'
' LookupValues Macro
    Set myRange = Application.InputBox( _
    prompt:="Select a Range", Type:=8)
    
    Set myPart = Application.InputBox( _
    prompt:="Select Part Number To Look Up", Type:=8)
    
    Set myPO = Application.InputBox( _
    prompt:="Select PO Number To Look Up", Type:=8)
    
    Set myPartC = Application.InputBox( _
    prompt:="Select Parts Column in Data Worksheet", Type:=8)
    
    Set myPOC = Application.InputBox( _
    prompt:="Select PO Column in Data Worksheet", Type:=8)
    
    Selection.FormulaArray = _
        "=INDEX(myRange,MATCH(myPart&myPO,myPartC&myPOC,0),7)"
End Sub

 

[vaskov17]

Try the code below. Note that when you enter the columns in the input box they should be entered as "A:A" instead of just "A". Another note is make sure when you enter the initial range the starting row is always 1, for example "A1:L20". If you enter "A2:L20", the formula will give you an incorrect answer.

Sub LookupValuesa()
'
' LookupValues Macro


    Dim myPart As Long
    Dim myPO As Long
    Dim myRange As Range
    Dim myPartC As Range
    Dim myPOC As Range
    
    Set myRange = Application.InputBox(prompt:="Select a Range", Type:=8)
    
    myPart = Application.InputBox(prompt:="Select Part Number To Look Up")
    
    myPO = Application.InputBox(prompt:="Select PO Number To Look Up")
    
    Set myPartC = Application.InputBox(prompt:="Select Parts Column in Data Worksheet", Type:=8)
    
    Set myPOC = Application.InputBox(prompt:="Select PO Column in Data Worksheet", Type:=8)
    
    Selection.FormulaArray = "=INDEX(" & myRange.Address & ",MATCH(" & myPart & "&" & myPO & "," & myPartC.Address & "&" & myPOC.Address & ",0),7)"
End Sub

 

[crystal_robinson]

I tried it and am getting an error at the input PO number part. I am just clicking on the PO number I need. Do I need to manually type everything into the input box? Im sorry I dont know what I am doing wrong. This is where its highlighted in the macro. Thanks for all your help.

myPO = Application.InputBox(prompt:="Select PO Number To Look Up")

 

[vaskov17]

Are myPO and myPart numbers you are typing in or are they cells you click on?

 

[vaskov17]

try this, haven't tested it so let me know if it works

Sub LookupValuesa()
'
' LookupValues Macro




    Dim myPart As Range
    Dim myPO As Range
    Dim myRange As Range
    Dim myPartC As Range
    Dim myPOC As Range
    
    Set myRange = Application.InputBox(prompt:="Select a Range", Type:=8)
    
    Set myPart = Application.InputBox(prompt:="Select Part Number To Look Up", Type:=8)
    
    Set myPO = Application.InputBox(prompt:="Select PO Number To Look Up", Type:=8)
    
    Set myPartC = Application.InputBox(prompt:="Select Parts Column in Data Worksheet", Type:=8)
    
    Set myPOC = Application.InputBox(prompt:="Select PO Column in Data Worksheet", Type:=8)
    
    Selection.FormulaArray = "=INDEX(" & myRange.Address & ",MATCH(" & myPart & "&" & myPO & "," & myPartC.Address & "&" & myPOC.Address & ",0),7)"
End Sub