ExcelBanter - Address of cell in VBA

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

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Address of cell in VBA (https://www.excelbanter.com/excel-worksheet-functions/260680-address-cell-vba.html)

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

Address of cell in VBA

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

Bob Phillips[_4_]

Address of cell in VBA

Try

cell.Address

--

HTH

Bob

"G.P.N.L. c.v.a." wrote in message
...
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

Mike H

Address of cell in VBA

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.

"G.P.N.L. c.v.a." wrote:

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

.

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

Address of cell in VBA

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.

"Mike H" wrote in message
...
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.

"G.P.N.L. c.v.a." wrote:

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

.

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

Address of cell in VBA

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!

"Bob Phillips" wrote in message
...
Try

cell.Address

--

HTH

Bob

"G.P.N.L. c.v.a." wrote in message
...
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

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

Address of cell in VBA

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.

"Mike H" wrote in message
...
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.

"G.P.N.L. c.v.a." wrote:

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

.

Mike H

Address of cell in VBA

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.

"G.P.N.L. c.v.a." wrote:

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.

"Mike H" wrote in message
...
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.

"G.P.N.L. c.v.a." wrote:

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

.

.

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

Address of cell in VBA

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

"Mike H" wrote in message
...
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.

"G.P.N.L. c.v.a." wrote:

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.

"Mike H" wrote in message
...
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.

"G.P.N.L. c.v.a." wrote:

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

.

.

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

Address of cell in VBA

Dave Peterson

Address of cell in VBA

Try:

Application.Caller.Address

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

"G.P.N.L. c.v.a." wrote:

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

--

Dave Peterson

Mike H

Address of cell in VBA

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.

"G.P.N.L. c.v.a." wrote:

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

.

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

Address of cell in VBA

Works !
TX, Dave
Gilbert

"Dave Peterson" wrote in message
...
Try:

Application.Caller.Address

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

"G.P.N.L. c.v.a." wrote:

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

--

Dave Peterson

All times are GMT +1. The time now is 11:41 AM.