ExcelBanter - Determining the Cell Address of a VLOOKUP Result in VBA

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Determining the Cell Address of a VLOOKUP Result in VBA (https://www.excelbanter.com/excel-discussion-misc-queries/163987-determining-cell-address-vlookup-result-vba.html)

Determining the Cell Address of a VLOOKUP Result in VBA

I'm currently implementing a user form that is meant to add new rows
or update existing rows on a worksheet. I'm currently using a VLookup
to determine if the item I want to add already exists on the
worksheet, and if it does, I'm loading the row information with
additional VLookups. The problem is, I need to be able to get back to
this source row in order to update it with any changes made to the
data in the user form. Does anyone have any suggestions as to how I
might be able to determine the row number or address of the cell that
is identified by the vlookup function? (I have enumerated constants
defined to handle my columns).

I should probably point out that I need to do this all in VBA. I
realize that I could store the address in a cell in the row and load
it into a variable when I load the rest of the rows data, but I'd
prefer to only store relevant data in the row and handle this
programmatically.

Thanks in advance for any help.
JR

Answer: Determining the Cell Address of a VLOOKUP Result in VBA

To determine the cell address of a VLOOKUP result in VBA, you can use the MATCH function in combination with the VLOOKUP function.

Here's an example code snippet that demonstrates how to do this:

Formula:


		
			
Dim lookupValue As Variant
Dim lookupRange As Range
Dim matchResult As Variant
Dim vlookupResult As Variant
Dim resultCell As Range

' Set the lookup value and range
lookupValue = "some value"
Set lookupRange = Range("A1:A10")

' Use the MATCH function to find the row number of the lookup value
matchResult = Application.Match(lookupValue, lookupRange, 0)

' Use the VLOOKUP function to get the result value
vlookupResult = Application.VLookup(lookupValue, lookupRange, 1, False)

' Use the Cells property to get a reference to the result cell
Set resultCell = Cells(matchResult, lookupRange.Column)

' Print the cell address and result value
Debug.Print "Result cell address: " & resultCell.Address
Debug.Print "Result value: " & vlookupResult

In this example, we first set the lookup value and range. We then use the MATCH function to find the row number of the lookup value in the range. We pass the row number and column number of the lookup range to the Cells property to get a reference to the result cell. Finally, we print the cell address and result value for verification.

Set the lookup value and range
Use the MATCH function to find the row number of the lookup value
Use the VLOOKUP function to get the result value
Use the Cells property to get a reference to the result cell
Print the cell address and result value

Determining the Cell Address of a VLOOKUP Result in VBA

Instead of VLOOKUP, use MATCH to find the (relative) row and assign this to a
variable. Then use the variable in INDEX to retrieve the other data as well
as to update the data if necessary.

Hope this helps.

Pete

" wrote:

I'm currently implementing a user form that is meant to add new rows
or update existing rows on a worksheet. I'm currently using a VLookup
to determine if the item I want to add already exists on the
worksheet, and if it does, I'm loading the row information with
additional VLookups. The problem is, I need to be able to get back to
this source row in order to update it with any changes made to the
data in the user form. Does anyone have any suggestions as to how I
might be able to determine the row number or address of the cell that
is identified by the vlookup function? (I have enumerated constants
defined to handle my columns).

I should probably point out that I need to do this all in VBA. I
realize that I could store the address in a cell in the row and load
it into a variable when I load the rest of the rows data, but I'd
prefer to only store relevant data in the row and handle this
programmatically.

Thanks in advance for any help.
JR

Determining the Cell Address of a VLOOKUP Result in VBA

=ADDRESS(MATCH(G4,A1:A10),1) works as a formula. The code is

Sub GetAddr()
Dim addr As String, rng As Range
Dim crit, X
Set crit = Range("g4")
Set rng = Range("A1:a10")
X = Application.Match(crit, rng, 0)
addr = Cells(X, 1).Address
MsgBox addr
End Sub

Regards
Peter

" wrote:

I'm currently implementing a user form that is meant to add new rows
or update existing rows on a worksheet. I'm currently using a VLookup
to determine if the item I want to add already exists on the
worksheet, and if it does, I'm loading the row information with
additional VLookups. The problem is, I need to be able to get back to
this source row in order to update it with any changes made to the
data in the user form. Does anyone have any suggestions as to how I
might be able to determine the row number or address of the cell that
is identified by the vlookup function? (I have enumerated constants
defined to handle my columns).

I should probably point out that I need to do this all in VBA. I
realize that I could store the address in a cell in the row and load
it into a variable when I load the rest of the rows data, but I'd
prefer to only store relevant data in the row and handle this
programmatically.

Thanks in advance for any help.
JR