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.

 

[Misca]

Welcome to the message board!

I'll have to warn you you're going to dive straight in the deep end if you're actually a VBA noob. This is one of those things that's quite a lot easier in Excel than in VBA but here's my 5 cents.

First create a standard module where you set up the actual lookup function:

Public Function MyLookUp(What As Variant, Where As Range, ColumnNo As Integer, SearchType As Boolean)

LookUpValue = Application.WorksheetFunction.VLookup(What, Where, ColumnNo, SearchType)

End Function

To have it fire whenever you select a cell on the target sheet you'll have to include a SelectionChange macro on that worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim MyRange As Range

Set MyRange = Sheet1.ListObjects(1).DataBodyRange   ' Set the Lookup Range here

If Target.Cells.Count <> 1 Then Exit Sub    'Only works when a single cell is selected

If Not Intersect(Target, Columns(2)) Is Nothing Then    ' If the selected cell ( = Target ) is in column B

On Error Resume Next        'Doesn't crash if there's a problem

    With Target
        .Value = MyLookUp(.Offset(, -1).Value, MyRange, 2, True) ' Enters the VLOOKUP value
    End With
End If

End Sub

If your lookup function always uses the same lookup range ( "Where" ), the same column ( "ColumnNo" ) and search type ( "SearchType" ) you can set these up in your actual lookup function. If you do this, you're only going to need the What parameter when you want to fire up your search function.

Whenever you're dealing with SelectionChange - or any other automatically fired macros - you should be as specific as possible to make sure when you don't wan't to fire the macros. If you don't know what you're doing, you're going to cause yourself a ton of problems if you don't do that because the macros will be running a lot more often than you'd think.

 

[ExcelNoob720]

Welcome to the message board!

I'll have to warn you you're going to dive straight in the deep end if you're actually a VBA noob. This is one of those things that's quite a lot easier in Excel than in VBA but here's my 5 cents.

First create a standard module where you set up the actual lookup function:

Public Function MyLookUp(What As Variant, Where As Range, ColumnNo As Integer, SearchType As Boolean)

LookUpValue = Application.WorksheetFunction.VLookup(What, Where, ColumnNo, SearchType)

End Function

To have it fire whenever you select a cell on the target sheet you'll have to include a SelectionChange macro on that worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim MyRange As Range

Set MyRange = Sheet1.ListObjects(1).DataBodyRange   ' Set the Lookup Range here

If Target.Cells.Count <> 1 Then Exit Sub    'Only works when a single cell is selected

If Not Intersect(Target, Columns(2)) Is Nothing Then    ' If the selected cell ( = Target ) is in column B

On Error Resume Next        'Doesn't crash if there's a problem

    With Target
        .Value = MyLookUp(.Offset(, -1).Value, MyRange, 2, True) ' Enters the VLOOKUP value
    End With
End If

End Sub

If your lookup function always uses the same lookup range ( "Where" ), the same column ( "ColumnNo" ) and search type ( "SearchType" ) you can set these up in your actual lookup function. If you do this, you're only going to need the What parameter when you want to fire up your search function.

Whenever you're dealing with SelectionChange - or any other automatically fired macros - you should be as specific as possible to make sure when you don't wan't to fire the macros. If you don't know what you're doing, you're going to cause yourself a ton of problems if you don't do that because the macros will be running a lot more often than you'd think.

Firstly, Thank you very much!
I am going to play around with it and follow up with any issues.

 

[Peter_SSs]

Welcome to the MrExcel board!

it runs a vlookup formula

What would the formula be?

another worbook as the table_array.

Is that other workbook open?

 

[ExcelNoob720]

Welcome to the message board!

I'll have to warn you you're going to dive straight in the deep end if you're actually a VBA noob. This is one of those things that's quite a lot easier in Excel than in VBA

@Misca, for simplicity purposes, my desire is to record a macro in Excel that allows me to accomplish my objective, but I was unable to record a macro that would execute it's function in any cell randomly selected before clicking the macro button.

If you have any insight on how to record a macro in this fashion, that would resolve my request too.

 

[ExcelNoob720]

Welcome to the MrExcel board!


What would the formula be?


Is that other workbook open?

To clarify further, here's the desired process flow:

1. select random cell (in this example, B1)

2. Click macro button which executes the following vlookup formula, selecting the first cell in the row as the lookup value... based on cell B1:
=vlookup(A1,[table array],[column#],FALSE)

3. macro populates the result of vlookup(A1...) in cell B1

4. move down the column to cell B5

5. click macro button... =vlookup(A5...)

6. macro populates the result of vlookup(A5...) in cell B5

7. and so on, and so forth

 

[Peter_SSs]

=vlookup(A1,[table array],[column#],FALSE)

I was hoping for an actual example formula to test with, especially since the table is apparently in another workbook


Confirmation about this?

Is that other workbook open?

 

[Misca]

@Misca, for simplicity purposes, my desire is to record a macro in Excel that allows me to accomplish my objective, but I was unable to record a macro that would execute it's function in any cell randomly selected before clicking the macro button.

If you have any insight on how to record a macro in this fashion, that would resolve my request too.

Click the "Use Relative References" button on your Developer tab to record relative macros:

 

[ExcelNoob720]

Click the "Use Relative References" button on your Developer tab to record relative macros:
View attachment 122252

Yes, this is how i select the "lookup_value" for my vlookup function (click: "record macro"... click: cell B2... Enter: "=vlookup("... [Press left arrow button once to select "A1" as lookup_value]... etc.)

However, when recording the macro, I begin by selecting cell B2... Which means it will always run from B2. Is there a way to avoid this?

How can I record a macro that when I select any cell in column B and run the macro, it scrolls one (or multiple) cells to the left and runs the vlookup formula using this cell as the lookup_value... Returning the vlookup result to the original cell in column B?

 

[Peter_SSs]

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.