Prev: How to combine Combo Box function with Vlookup function
Next: WHEN ENTER NUMBER , DISPLAY IN WARDS ?
From: Mike H on 5 Apr 2010 08:51
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
>
>
> .
>
From: G.P.N.L. c.v.a. on 5 Apr 2010 09:17
Works !
TX, Dave
Gilbert

"Dave Peterson" <petersod(a)verizonXSPAM.net> wrote in message
news:4BB9D50D.6A9A8F0F(a)verizonXSPAM.net...
> 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  | 
Pages: 1 2 3
Prev: How to combine Combo Box function with Vlookup function
Next: WHEN ENTER NUMBER , DISPLAY IN WARDS ?