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

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:

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.

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

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

=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