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.
 
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:

1739421838694.png


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

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

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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,516
Messages
6,191,499
Members
453,659
Latest member
thomji1

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