0

I'm looking to use an active cell as reference to return values from another sheet using a vlookup. I have set my table array and that seems to work fine.

I run the macro and nothing happens- no errors and the message box at the end will not show. I know the active cell matches a value in the table I am referencing so there should not be an N/A error.

Then end goal is to have a Msgbox with last name and first name after clicking a button with this macro assigned. 

Sub ContactInfo()

Dim name As String
Dim ArrayRange As Variant
Dim ActCell As Variant

Set ActCell = ActiveCell.Value

Sheets("results").Select
ArrayRange = Sheets("Results").Range(Range("L1"), Range("L1").SpecialCells(xlLastCell))

Lastname = Application.WorksheetFunction.VLookup(ActCell, ArrayRange, 3, False)
Firstname = Application.WorksheetFunction.VLookup(ActCell, ArrayRange, 2, False)

MsgBox Lastname & Firstname


End Sub
Community
  • 1
  • 1
carter
  • 75
  • 9
  • Toss some breakpoints in there (Place your cursor on a line of code and hit F9). Then execute. Does the code execution stop on your breakpoint? If so, set the breakpoint farther down the code. Something funky is happening as your `msgbox` should absolutely fire (or an error should be thrown). – JNevill May 22 '18 at 20:04
  • That is so weird.... I figured it out after setting a breakpoint. I needed to re-select the initial sheet with the target activecell highlighted. Thank you – carter May 22 '18 at 20:12
  • 1
    What do you expect from `Range("L1").SpecialCells(xlLastCell)`? – JohnyL May 22 '18 at 20:13
  • @JohnyL It is selecting my table in another sheet. All columns to the last row of data – carter May 22 '18 at 20:19
  • 2
    @carter it's not weird at all. Most bugs happen to be identifiable using the debugger. That said when you hit an error it's usually a good idea to include its description, and specifically which statement is throwing the error (make sure your VBE settings are set to "break on unhandled errors"). Lastly, read up on [avoiding Select and Activate](https://stackoverflow.com/q/10714251/1188513), you don't need to `Select` anything here. – Mathieu Guindon May 22 '18 at 20:19
  • 2
    @carter It operates *only* on cell `L1` – JohnyL May 22 '18 at 20:31

0 Answers0