Address of cell in VBA

  • Thread starter Thread starter G.P.N.L. c.v.a.
  • Start date Start date
G

G.P.N.L. c.v.a.

I want to make a UDF,
in which I use the address of the cell itself in VLOOKUP.

How do I define the address of the cell ?

cell("address") give an error

Tx,
Gilbert
 
Hi,

Address is a property of a range object so along these lines

r = ActiveCell.Address

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
This is OK as long as the cursor is in the cell you want.
If I put this address in X1, it gives indeed a VLOOPUP with X2 as the first
argument,
but when X2 is active, the result in X1 is a VLOOKUP with X2 as the first
argument.
 
Does not work, Bob,

Function ZKP() As String

Dim RESULT As String

ADRES = Cell.Address

RESULT = WorksheetFunction. _
VLookup(ADRES, _
Workbooks("CONNECTIONS.xls"). _
Sheets("DB"). _
Range("db" & Left(ActiveWorkbook.Name,
Len(ActiveWorkbook.Name) - 4)), _
2, _
False)
ZKP = RESULT

End Function

gives #VALUE!
 
CORRECTION :
This is OK as long as the cursor is in the cell you want.
If I put this address in X1, it gives indeed a VLOOPUP with X1 as the first
argument,
but when X2 is active, the result in X1 is a VLOOKUP with X2 as the first
argument.
 
Hi,

I don't understand that. Post the vb code that isn't working and we'll see
what we can do from there.
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Function ZKP() As String

Dim RESULT As String

ADRES = ActiveCell.Address

RESULT = WorksheetFunction. _
VLookup(ADRES, _
Workbooks("CONNECTIONS.xls"). _
Sheets("DB"). _
Range("db" & Left(ActiveWorkbook.Name,
Len(ActiveWorkbook.Name) -
4)), _
2, _
False)
ZKP = RESULT

End Function


What I mean, Mike, is that when I put "ZKP()" in X1, do <CR>, the right
answer comes in X1
but then X2 is the Active Cell.
When I do <F9> (recalculate) then, X2 is the active cell, and the answer
shown in X1, is the one that should come in X2.
I want to use ZKP() in several cells, each looking for their own value.
Does that explain what I'm looking for ?
(Please also see Bob Phillips's reply)
Regards,
Gilbert
 
Function ZKP() As String

Dim RESULT As String

ADRES = ActiveCell.Address

RESULT = WorksheetFunction. _
VLookup(ADRES, _
Workbooks("CONNECTIONS.xls"). _
Sheets("DB"). _
Range("db" & Left(ActiveWorkbook.Name,
Len(ActiveWorkbook.Name) -
4)), _
2, _
False)
ZKP = RESULT

End Function


What I mean, Mike, is that when I put "ZKP()" in X1, do <CR>, the right
answer comes in X1
but then X2 is the Active Cell.
When I do <F9> (recalculate) then, X2 is the active cell, and the answer
shown in X1, is the one that should come in X2.
I want to use ZKP() in several cells, each looking for their own value.
Does that explain what I'm looking for ?
(Please also see Bob Phillips's reply)
Regards,
Gilbert
 
Try:

Application.Caller.Address

This returns the address of the cell(s) that holds the function in the worksheet
cell(s).
 
Hi,

I haven't tried to understand you vlookup formula but the line

ADRES = ActiveCell.Address
will return the address of that celll and what i thing you want is the value
in the cell so maybe you mean

ADRES = ActiveCell.Value

having said that this is going to give problem whenever the function
re-valculates if the 'wrong' cell is active. what you should be doing is
passing an argument to your function like this

Function ZKP(ADRES As Range) As String
Dim RESULT As String
LookupVal = ADRES.Value
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.
 
Works !
TX, Dave
Gilbert

Dave Peterson said:
Try:

Application.Caller.Address

This returns the address of the cell(s) that holds the function in the
worksheet
cell(s).
Click to expand...
 
Back
Top