Prev: How to combine Combo Box function with Vlookup function
Next: WHEN ENTER NUMBER , DISPLAY IN WARDS ?
From: G.P.N.L. c.v.a. on 5 Apr 2010 06:22
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" <MikeH(a)discussions.microsoft.com> wrote in message
news:D197DF2E-F774-4DB3-88AD-264B40616412(a)microsoft.com...
> 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
>>
>>
>> .
>>


From: Mike H on 5 Apr 2010 06:51
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" <MikeH(a)discussions.microsoft.com> wrote in message
> news:D197DF2E-F774-4DB3-88AD-264B40616412(a)microsoft.com...
> > 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
> >>
> >>
> >> .
> >>
>
>
> .
>
From: G.P.N.L. c.v.a. on 5 Apr 2010 07:24
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" <MikeH(a)discussions.microsoft.com> wrote in message
news:6F47BBCC-703E-4EDE-8BCC-C64D7096C83C(a)microsoft.com...
> 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" <MikeH(a)discussions.microsoft.com> wrote in message
>> news:D197DF2E-F774-4DB3-88AD-264B40616412(a)microsoft.com...
>> > 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
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>


From: G.P.N.L. c.v.a. on 5 Apr 2010 07:29
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


From: Dave Peterson on 5 Apr 2010 08:18
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
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: How to combine Combo Box function with Vlookup function
Next: WHEN ENTER NUMBER , DISPLAY IN WARDS ?