On Mar 7, 9:07*pm, "Mike Fogleman" wrote:
Trying to understand the difference in your 2 criteria. It appears criteria1
applies to the first 6 months while criteria2 applies to the second 6
months, looking for a number in each set of months.If it finds a number in
criteria1 then use that number, else use the number from criteria2. My
question is: since the answer will always go in the cell where the function
is, what does it matter which half of the year it found it in? There is only
going to be 1 number on that row for the entire year. It seems to me that
there is only 1 critria, non-blank & isnumber.Find that and do the
calculation.
If I'm wrong, please explain why.

Mike F

"Pelham" wrote in message

...
I refer to the highly informative link below:

http://j-walk.com/ss//excel/usertips/tip080.htm

I am trying to develop aVBAfunction procedure that will run along a
row of 12 cells (one for each month of the year) and perform a simple
calculation and enter the result of that calculation in the cell where
theVBAfunction is called from. There is only ever one value per row
(as the rest of the cells in that row are always blank), so once it
has found the only non-blank value in that row then the operation can
cease searching that row.

You can see from the following formula (thanks to the above link) what
I am trying to achieve:

=IF(Criteria1,Criteria1,Criteria2)

Where Criteria 1 is:

=IF(ISNUMBER(ºñ!I10),(ºñ!I$8*ºñ!I$7)-ºñ!I10,IF(ISNUMBER(ºñ!K10),(ºñ!K$8*ºñ!*K
$7)-ºñ!K10,IF(ISNUMBER(ºñ!M10),(ºñ!M$8*ºñ!M$7)-ºñ!M10,IF(ISNUMBER(ºñ!O10),(*ºñ!
O$8*ºñ!O$7)-ºñ!O10,IF(ISNUMBER(ºñ!Q10),(ºñ!Q$8*ºñ!Q$7)-ºñ!Q10,IF(ISNUMBER(º*ñ!
S10),(ºñ!S$8*ºñ!S$7)-ºñ!S10,""))))))

Where Criteria 2 is:

=IF(ISNUMBER(ºñ!U10),(ºñ!U$8*ºñ!U$7)-ºñ!U10,IF(ISNUMBER(ºñ!W10),(ºñ!W$8*ºñ!*W
$7)-ºñ!W10,IF(ISNUMBER(ºñ!Y10),(ºñ!Y$8*ºñ!Y$7)-ºñ!Y10,IF(ISNUMBER(ºñ!AA10),
(ºñ!AA$8*ºñ!AA$7)-ºñ!AA10,IF(ISNUMBER(ºñ!AC10),(ºñ!AC$8*ºñ!AC$7)-ºñ!
AC10,IF(ISNUMBER(ºñ!AE10),(ºñ!AE$8*ºñ!AE$7)-ºñ!AE10,""))))))

Can someone please help get me started with aVBAfunction procedure
that will do the same thing as this long formula¨C please!?!?

Many thanks!

Mike - you are spot on. The procedure is that simple - it just finds
the only number in that row and do the calculation. I just want to
know how I can make a VBA function that does this...

Thank you again.