Run macro to return result to selected cell

ExcelNoob720

New Member
Joined
Feb 7, 2025
Messages
6
Office Version
  1. 2024
Platform
  1. Windows
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.
 
Sounds good, thanks!

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

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

1739421838694.png


Book2:
  • Select cell B2
  • Run macro
1739421863909.png

  • Select cell B3
  • Run macro
1739421923057.png
 
Upvote 0

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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).

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,226,527
Messages
6,191,570
Members
453,665
Latest member
WaterWorks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top