CODEWITHSUNDEEP
vbavlookup
Mary Ellen
Mary Ellen

Reputation: 41

return a row number from a vlookup in vba

I need to know what code to use in this situation.

I am using vlookup in vba to locate a certain record number. I then need to know what row number the record is located in. I have tried the following, but received errors:

nRowSavedRecord = [Vlookup(Cells(nRows, nColRecNmbr),Range("RecordInfo"),2,False).Row]

This gives me a "Type Mismatch" error.

or

nRowSavedRecord = Application.vlookup(cells(nRows, nColRecNmbr), Range("RecordInfo"),2,False).Rows

This gives me an "Object Required" error.

I'm sure whatever I'm missing is simple.
(nRowSavedRecord is a Long) Can anyone help?

Thanks!

Upvotes: 4

Views: 34806

Answers (2)

martin
martin

Reputation: 2638

There are several problems in your question.

  • VLookup is not property of Application, but of Application.WorksheetFunction
  • VLookup returns value, not reference to a cell, so you cannot get row from it

You probably want to use the Match function, for example like this:

nRowSavedRecord = Application.WorksheetFunction.Match(Cells(nRows, nColRecNmbr), Range("RecordInfo"), 0)

The last 0 means that this is an exact match. You also have to make sure that RecordInfo is a one-dimensional range

Upvotes: 5

Rich Andrews
Rich Andrews

Reputation: 4188

Have you tried using the Match function?

http://office.microsoft.com/en-gb/excel-help/match-function-HP010062414.aspx

Upvotes: 2

Related Questions