Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF: how to get value of the argument's adjacent cell?
Hi
I need to write a function which will use two arguments, e.g. vala and valb (not adjacent) as input values. This function should refer adjacent cells of vala and valb to calculate result for example: A B 1 adj_vala vala 2 3 adj_valb valb 4 adj_vala + adj_valb So UDF to calculate B4, as I see it, will lool like (I don't know how to get adj_vala and adj_valb in the UDF below): Function Result(vala As Long, valb As Long) As Long Dim adj_vala As Long Dim adj_valb As Long ' get value of the left adjacent cell of vala adj_vala = ??? ' get value of the left adjacent cell of valb adj_valb = ??? Result = adj_vala + adj_valb End Function The example above describes simplifed version of more complex logic I have to implement, but idea will be the same... Thanks a lot, Andrew |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get value of the argument's adjacent cell?
One way:
Function Result(vala As Range, valb As Range) As Long Result = vala.Offset(, -1) + valb.Offset(, -1) End Function The problem is that you say you want to use values as arguments but you are using the ranges that the values are in to calculate the results. The above takes care of that disconnect. -- Vasant "Andrew" wrote in message om... Hi I need to write a function which will use two arguments, e.g. vala and valb (not adjacent) as input values. This function should refer adjacent cells of vala and valb to calculate result for example: A B 1 adj_vala vala 2 3 adj_valb valb 4 adj_vala + adj_valb So UDF to calculate B4, as I see it, will lool like (I don't know how to get adj_vala and adj_valb in the UDF below): Function Result(vala As Long, valb As Long) As Long Dim adj_vala As Long Dim adj_valb As Long ' get value of the left adjacent cell of vala adj_vala = ??? ' get value of the left adjacent cell of valb adj_valb = ??? Result = adj_vala + adj_valb End Function The example above describes simplifed version of more complex logic I have to implement, but idea will be the same... Thanks a lot, Andrew |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get value of the argument's adjacent cell?
Change your function definition to take range parameters instead of
longs Function Result(rng1 As Range, rng2 As Range) As Long Dim adj_vala As Long Dim adj_valb As Long ' get value of the left adjacent cell of vala adj_vala = rng1.offset(0,-1).value adj_valb = rng2.offset(0,-1).value Result = adj_vala + adj_valb End Function Tim. "Andrew" wrote in message om... Hi I need to write a function which will use two arguments, e.g. vala and valb (not adjacent) as input values. This function should refer adjacent cells of vala and valb to calculate result for example: A B 1 adj_vala vala 2 3 adj_valb valb 4 adj_vala + adj_valb So UDF to calculate B4, as I see it, will lool like (I don't know how to get adj_vala and adj_valb in the UDF below): Function Result(vala As Long, valb As Long) As Long Dim adj_vala As Long Dim adj_valb As Long ' get value of the left adjacent cell of vala adj_vala = ??? ' get value of the left adjacent cell of valb adj_valb = ??? Result = adj_vala + adj_valb End Function The example above describes simplifed version of more complex logic I have to implement, but idea will be the same... Thanks a lot, Andrew |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get value of the argument's adjacent cell?
You got solid advice.
Just to add, I think you will have to change your function name from "Result" to something else. Excel doesn't seem to like it as a UDF name. "Andrew" wrote in message om... Hi I need to write a function which will use two arguments, e.g. vala and valb (not adjacent) as input values. This function should refer adjacent cells of vala and valb to calculate result for example: A B 1 adj_vala vala 2 3 adj_valb valb 4 adj_vala + adj_valb So UDF to calculate B4, as I see it, will lool like (I don't know how to get adj_vala and adj_valb in the UDF below): Function Result(vala As Long, valb As Long) As Long Dim adj_vala As Long Dim adj_valb As Long ' get value of the left adjacent cell of vala adj_vala = ??? ' get value of the left adjacent cell of valb adj_valb = ??? Result = adj_vala + adj_valb End Function The example above describes simplifed version of more complex logic I have to implement, but idea will be the same... Thanks a lot, Andrew |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get value of the argument's adjacent cell?
Hi Andrew,
Be aware that your function might not recalculate when expected. Excel will recalc when (one of) the arguments change, not cells adjacent to the arguments. -- Kind Regards, Niek Otten Microsoft MVP - Excel "Andrew" wrote in message om... Hi I need to write a function which will use two arguments, e.g. vala and valb (not adjacent) as input values. This function should refer adjacent cells of vala and valb to calculate result for example: A B 1 adj_vala vala 2 3 adj_valb valb 4 adj_vala + adj_valb So UDF to calculate B4, as I see it, will lool like (I don't know how to get adj_vala and adj_valb in the UDF below): Function Result(vala As Long, valb As Long) As Long Dim adj_vala As Long Dim adj_valb As Long ' get value of the left adjacent cell of vala adj_vala = ??? ' get value of the left adjacent cell of valb adj_valb = ??? Result = adj_vala + adj_valb End Function The example above describes simplifed version of more complex logic I have to implement, but idea will be the same... Thanks a lot, Andrew |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF: how to get value of the argument's adjacent cell?
David Prout wrote in message . ..
Add the function below into a module Function Res(ValA, ValB) Res = ValA * ValB End Function in cell B4 (or anywhere) type =res(A1,A3) DavidP On 12 Apr 2004 21:42:12 -0700, (Andrew) wrote: Hi I need to write a function which will use two arguments, e.g. vala and valb (not adjacent) as input values. This function should refer adjacent cells of vala and valb to calculate result for example: A B 1 adj_vala vala 2 3 adj_valb valb 4 adj_vala + adj_valb So UDF to calculate B4, as I see it, will lool like (I don't know how to get adj_vala and adj_valb in the UDF below): Function Result(vala As Long, valb As Long) As Long Dim adj_vala As Long Dim adj_valb As Long ' get value of the left adjacent cell of vala adj_vala = ??? ' get value of the left adjacent cell of valb adj_valb = ??? Result = adj_vala + adj_valb End Function The example above describes simplifed version of more complex logic I have to implement, but idea will be the same... Thanks a lot, Andrew Hi David, thanks for your reply but I need to get values of A1 and A3 inside my function, and my function shouldn't use A1 and A3 value as arguments, arguments are B1 and B3, not A1 and A3!!! As I said this is a simplified logic I am going to use, complete solution will scan adjacent cell untill a condition will be met. In order to implement this I have to somehow get A1 and A3 values in my function. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF: how to get value of the argument's adjacent cell?
And did the many other solutions offered not accomplish that?
-- Vasant "Andrew" wrote in message om... David Prout wrote in message . .. Add the function below into a module Function Res(ValA, ValB) Res = ValA * ValB End Function in cell B4 (or anywhere) type =res(A1,A3) DavidP On 12 Apr 2004 21:42:12 -0700, (Andrew) wrote: Hi I need to write a function which will use two arguments, e.g. vala and valb (not adjacent) as input values. This function should refer adjacent cells of vala and valb to calculate result for example: A B 1 adj_vala vala 2 3 adj_valb valb 4 adj_vala + adj_valb So UDF to calculate B4, as I see it, will lool like (I don't know how to get adj_vala and adj_valb in the UDF below): Function Result(vala As Long, valb As Long) As Long Dim adj_vala As Long Dim adj_valb As Long ' get value of the left adjacent cell of vala adj_vala = ??? ' get value of the left adjacent cell of valb adj_valb = ??? Result = adj_vala + adj_valb End Function The example above describes simplifed version of more complex logic I have to implement, but idea will be the same... Thanks a lot, Andrew Hi David, thanks for your reply but I need to get values of A1 and A3 inside my function, and my function shouldn't use A1 and A3 value as arguments, arguments are B1 and B3, not A1 and A3!!! As I said this is a simplified logic I am going to use, complete solution will scan adjacent cell untill a condition will be met. In order to implement this I have to somehow get A1 and A3 values in my function. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to get value of the argument's adjacent cell?
I may be missing something, but surely you would pass the 2 cells to be
worked upon as arguments to that function and use them. Thus if you wanted to work on B4 and B5 you would cal.l =myFunction(B4,B5) To use =myFun ction(B3,B4) and offset them seems pervers to me. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Andrew" wrote in message om... Hi I need to write a function which will use two arguments, e.g. vala and valb (not adjacent) as input values. This function should refer adjacent cells of vala and valb to calculate result for example: A B 1 adj_vala vala 2 3 adj_valb valb 4 adj_vala + adj_valb So UDF to calculate B4, as I see it, will lool like (I don't know how to get adj_vala and adj_valb in the UDF below): Function Result(vala As Long, valb As Long) As Long Dim adj_vala As Long Dim adj_valb As Long ' get value of the left adjacent cell of vala adj_vala = ??? ' get value of the left adjacent cell of valb adj_valb = ??? Result = adj_vala + adj_valb End Function The example above describes simplifed version of more complex logic I have to implement, but idea will be the same... Thanks a lot, Andrew |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Force entry into cell, based on validation selection in adjacent cell | Excel Worksheet Functions | |||
How to populate a cell with numeric value based on textselected from pull down in adjacent cell? | Excel Worksheet Functions | |||
Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
When data match, copy adjacent value to adjacent column | Excel Worksheet Functions | |||
In adjacent cell, show last date modified of target cell. | Excel Discussion (Misc queries) |