0

I have a button that will vlookup the selected cell's text and reference a table in different sheet to return last name, first name. Here's what I have:

Sub button1()
Dim name As String
Dim ArrayRange As Range
Dim ActCell As Range

Set ActCell = ActiveCell


Sheets("Results").Select
Set ArrayRange = Sheets("Results").Range("A1", Range("A1").End(xlToRight).End(xlDown))
Sheets(1).Select

Lastname = Application.WorksheetFunction.VLookup(ActCell.Value, ArrayRange, 13, False)
FirstName = Application.WorksheetFunction.VLookup(ActCell.Value, ArrayRange, 12, False)

MsgBox Lastname & FirstName
End Sub

I am getting the error

unable to get the vLookup property of the WorksheetFunction class

Any help would be greatly appreciated!

Community
  • 1
  • 1
carter
  • 75
  • 9
  • Looks like your range is a problem, since makes ArrayRange fail. Get rid of the second A1 and it should be fine. I would recommend making some dynamic variables and use .Range(.Cells(),.Cells()), though. – Cyril May 22 '18 at 16:26
  • @Cyril - modified to Set ArrayRange = Sheets("Results").Range("A1").End(xlToRight).End(xlDown) and got the same error – carter May 22 '18 at 16:30
  • Look at what you've got: Sheets("Results").Range(**"A1", Range("A1").End(xlToRight).End(xlDown)**) you're not using the right syntax. sheets().range( sheets.cells(), sheets.cells()) or sheets()range("a1:a"& sheets.range("a1").end(xldown)) would be appropriate – Cyril May 22 '18 at 17:33
  • I do not get an error, but I populated my sheet according to your code. There can be no blank cells in Row 1 between column A to Column N Because you are using `xlToRight` Possible using the last cell in the sheet would work better..`Set ArrayRange = Range(Range("A1"), Range("A1").SpecialCells(xlLastCell)) ` – Davesexcel May 22 '18 at 17:47
  • You could use. `Set ArrayRange = Range(Cells(1, 1), Cells(Cells(Rows.Count, "A").End(xlUp).Row, Cells(1, Columns.Count).End(xlToLeft).Column))` – Davesexcel May 22 '18 at 17:53
  • @Cyril I got the range to work however the message box doesn't do anything - no error just goes blank. I appreciate your help – carter May 22 '18 at 19:21
  • @Davesexcel Thanks - please see the above comment – carter May 22 '18 at 19:22

0 Answers0