Run macro to return result to selected cell

[ExcelNoob720]

Hello!

I'm trying to set up a macro button that, when clicked, will run a vlookup formula and return the resulting value to whichever cell i have selected.

Desired result: in column A i have a list of identifiers. I click into cell B1, i click my macro button and it runs a vlookup formula using cell A1 as the lookup_value and another worbook as the table_array. The resulting value of the vlookup is populated in cell B1.

Thanks in advance for your attention and assistance.

 

[ExcelNoob720]

You can achieve what you want. However, since you are using vba, I would use vba code directly to find the required result rather than using vba to go back to a worksheet VLOOKUP formula.
I asked for an example of an actual formula that you are using (or one that you might use) so that I can convert the process to vba and show you the result.
If you insist on using the VLOOKUP formula, that is fine, but again I would want a specific formula so that I can test it and give you the code for it.

Sounds good, thanks!

Example formula: =VLOOKUP(A2,[Book1.xlsx]Sheet1!$A:$B,2,FALSE)

Here's how I envision it...
Book1:

Book2:

• Select cell B2
• Run macro

• Select cell B3
• Run macro

 

[Peter_SSs]

Thanks for the further information. I am expecting to be away from the forum for a few days so I have gone to the VLOOKUP idea you were originally asking about rather than what I was discussing in post #10. The code is triggered by double-clicking in a cell in column B of Book2 rather than just selecting it and then running the macro by clicking a button as you originally suggested.
To implement ..
1. Right click the sheet name tab of the sheet where you want the result to appear and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test by double-clicking cells in column B.
4. Book1 does need to be open at the time. (I did also ask about that twice but didn't get an answer. )
5. Your workbook with this code will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  If Target.Column = 2 And Target.Row > 1 Then
    Cancel = True
    Target.Value = Evaluate("VLOOKUP(" & Target.Offset(, -1).Address & ",[Book1.xlsx]Sheet1!$A:$B,2,FALSE)")
  End If
End Sub