Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding IF limits with VBA
I refer to the highly informative link below:
http://j-walk.com/ss//excel/usertips/tip080.htm I am trying to develop a VBA function 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 the VBA function 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 a VBA function procedure that will do the same thing as this long formula¨C please!?!? Many thanks! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoiding #N/A | Excel Discussion (Misc queries) | |||
Avoiding #N/A | Excel Discussion (Misc queries) | |||
avoiding multiplication | Excel Discussion (Misc queries) | |||
Avoiding #value | Excel Worksheet Functions | |||
Avoiding #NUM! | Excel Worksheet Functions |