| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . . |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . . |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 . |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| =HYPERLINK("#"&CELL("address", ADDRESS(MATCH(VALUE(B3),QuoteNotes, | Excel Worksheet Functions | |||
| =HYPERLINK("#"&CELL("address", ADDRESS(MATCH(VALUE(B3),Range ... ? | Excel Worksheet Functions | |||
| Return cell address of a cell based on contents of cell. | Excel Worksheet Functions | |||
| find last cell in range with data, display cell address | Excel Worksheet Functions | |||
| How make hyperlink refer to cell content rather than cell address. | Excel Discussion (Misc queries) | |||
Hybrid Mode
