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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding IF limits with VBA
can you say the range of this 12 cells ?
also , specificate the type of calculation and then the cells you want to place the result , and the things will become clear ; Respectfully |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding IF limits with VBA
On Mar 7, 1:30*pm, ytayta555 wrote:
can you say the range of this 12 cells ? also , specificate the type of calculation and then the cells you want to place the result , and the things will become clear ; Respectfully Many thanks, ytayta555! The range is always the same row as the cell where the function is being called from. If the row is row 10 (as in the example above), then the range is every other cell from and including I10 to AE10 - therefore I10, K10, M10... etc until AE10. These are the 12 cells in the range! The calculation is always the same style, so for I10 it is: (I8*I7) - I10 and for K10 for example it is: (K8*K7) - K10. The result is always in the G cell on that row, so for example it is G10 for row 10 and G12 for row 12. Does this make sense? If you look at the formuals I copeis above you will see the patterns... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding IF limits with VBA
HI
Here you have a example where all formulas work with values of I8*I7 - I10 range , and put the result in range from G10 to G21 : Sub Macrocomanda6() ' ' Macrocomanda6 Macrocomanda ' Macrocomanda înregistrata 07.03.2008 de ytayta ' ' Range("G10").Select ActiveCell.FormulaR1C1 = "=(R[-2]C[2]*R[-3]C[2])-RC[2]" Range("G11").Select ActiveCell.FormulaR1C1 = "=(R[-3]C[2]*R[-4]C[2])-R[-1]C[2]" Range("G12").Select ActiveCell.FormulaR1C1 = "=(R[-4]C[2]*R[-5]C[2])-R[-2]C[2]" Range("G13").Select ActiveCell.FormulaR1C1 = "=(R[-5]C[2]*R[-6]C[2])-R[-3]C[2]" Range("G14").Select ActiveCell.FormulaR1C1 = "=(R[-6]C[2]*R[-7]C[2])-R[-4]C[2]" Range("G15").Select ActiveCell.FormulaR1C1 = "=(R[-7]C[2]*R[-8]C[2])-R[-5]C[2]" Range("G16").Select ActiveCell.FormulaR1C1 = "=(R[-8]C[2]*R[-9]C[2])-R[-6]C[2]" Range("G17").Select ActiveCell.FormulaR1C1 = "=(R[-9]C[2]*R[-10]C[2])-R[-7]C[2]" Range("G18").Select ActiveCell.FormulaR1C1 = "=(R[-10]C[2]*R[-11]C[2])-R[-8]C[2]" Range("G19").Select ActiveCell.FormulaR1C1 = "=(R[-11]C[2]*R[-12]C[2])-R[-9]C[2]" Range("G20").Select ActiveCell.FormulaR1C1 = "=(R[-12]C[2]*R[-13]C[2])-R[-10]C[2]" Range("G21").Select ActiveCell.FormulaR1C1 = "=(R[-13]C[2]*R[-14]C[2])-R[-11]C[2]" End Sub The result is always in the G cell on that row, so for example it is G10 for row 10 and G12 for row 12. ...here is a misunderstand , where you want macro to place the results ...you said you want to have the result for every I10 ,K10, M10 , O10.......AE10 cells ! How can put the 12 results in the same cell ? Respectfully Hope to help |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding IF limits with VBA
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 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding IF limits with VBA
Hi
Put this code in a module and type "=yCalc(10)" in G10 and see if you get the right result. Function yCalc(TargetRow As Long) As Long Application.Volatile Dim fCol As Long Dim Result As Long fCol = 9 For r = 0 To 22 Step 2 If IsNumeric(Cells(TargetRow, fCol + r)) And IsNumeric(Cells(TargetRow - 3, fCol + r)) _ And IsNumeric(Cells(TargetRow - 2, fCol + r)) Then Result = Result + (Cells(TargetRow - 2, fCol + r).Value _ * Cells(TargetRow - 3, fCol + r).Value) - Cells(TargetRow, fCol + r).Value End If Next yCalc = Result End Function Regards, Per On 7 Mar., 06:21, Pelham wrote: On Mar 7, 1:30*pm, ytayta555 wrote: can you say the range of this 12 cells ? also , specificate the type of calculation and then the cells you want to place the result , and the things will become clear ; Respectfully Many thanks, ytayta555! The range is always the same row as the cell where the function is being called from. If the row is row 10 (as in the example above), then the range is every other cell from and including I10 to AE10 - therefore I10, K10, M10... etc until AE10. These are the 12 cells in the range! The calculation is always the same style, so for I10 it is: (I8*I7) - I10 and for K10 for example it is: *(K8*K7) - K10. The result is always in the G cell on that row, so for example it is G10 for row 10 and G12 for row 12. Does this make sense? If you look at the formuals I copeis above you will see the patterns... |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding IF limits with VBA
On Mar 7, 3:33*pm, ytayta555 wrote:
HI Here you have a example where all *formulas work with values of *I8*I7 - I10 range , and put the result in range from G10 *to *G21 *: Sub Macrocomanda6() ' ' Macrocomanda6 Macrocomanda ' Macrocomanda înregistrata *07.03.2008 de ytayta ' ' * * Range("G10").Select * * ActiveCell.FormulaR1C1 = "=(R[-2]C[2]*R[-3]C[2])-RC[2]" Range("G11").Select * * ActiveCell.FormulaR1C1 = "=(R[-3]C[2]*R[-4]C[2])-R[-1]C[2]" Range("G12").Select * * ActiveCell.FormulaR1C1 = "=(R[-4]C[2]*R[-5]C[2])-R[-2]C[2]" Range("G13").Select * * ActiveCell.FormulaR1C1 = "=(R[-5]C[2]*R[-6]C[2])-R[-3]C[2]" Range("G14").Select * * ActiveCell.FormulaR1C1 = "=(R[-6]C[2]*R[-7]C[2])-R[-4]C[2]" Range("G15").Select * * ActiveCell.FormulaR1C1 = "=(R[-7]C[2]*R[-8]C[2])-R[-5]C[2]" Range("G16").Select * * ActiveCell.FormulaR1C1 = "=(R[-8]C[2]*R[-9]C[2])-R[-6]C[2]" Range("G17").Select * * ActiveCell.FormulaR1C1 = "=(R[-9]C[2]*R[-10]C[2])-R[-7]C[2]" Range("G18").Select * * ActiveCell.FormulaR1C1 = "=(R[-10]C[2]*R[-11]C[2])-R[-8]C[2]" Range("G19").Select * * ActiveCell.FormulaR1C1 = "=(R[-11]C[2]*R[-12]C[2])-R[-9]C[2]" Range("G20").Select * * ActiveCell.FormulaR1C1 = "=(R[-12]C[2]*R[-13]C[2])-R[-10]C[2]" Range("G21").Select * * ActiveCell.FormulaR1C1 = "=(R[-13]C[2]*R[-14]C[2])-R[-11]C[2]" End Sub The result is always in the G cell on that row, so for example it is G10 for row 10 and G12 for row 12. ...here is a misunderstand *, where you want *macro to place the results ...you said you want to have the result for every *I10 *,K10, M10 , O10.......AE10 cells ! How can put the 12 results in the same cell ? Respectfully Hope to help Good question! Only one of the 12 cells has a number because the rest are blank cells, so only one result will go in the cell. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding IF limits with VBA
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding IF limits with VBA
On Mar 8, 4:49*am, Per Jessen wrote:
Hi Put this code in a module and type "=yCalc(10)" in G10 and see if you get the right result. Function yCalc(TargetRow As Long) As Long Application.Volatile Dim fCol As Long Dim Result As Long fCol = 9 For r = 0 To 22 Step 2 * * If IsNumeric(Cells(TargetRow, fCol + r)) And IsNumeric(Cells(TargetRow - 3, fCol + r)) _ * * * * And IsNumeric(Cells(TargetRow - 2, fCol + r)) Then * * * * Result = Result + (Cells(TargetRow - 2, fCol + r).Value _ * * * * * Cells(TargetRow - 3, fCol + r).Value) - Cells(TargetRow, fCol + r).Value * * End If Next yCalc = Result End Function Regards, Per On 7 Mar., wrote: On Mar 7, 1:30*pm, ytayta555 wrote: can you say the range of this 12 cells ? also , specificate the type of calculation and then the cells you want to place the result , and the things will become clear ; Respectfully Many thanks, ytayta555! The range is always the same row as the cell where the function is being called from. If the row is row 10 (as in the example above), then the range is every other cell from and including I10 to AE10 - therefore I10, K10, M10... etc until AE10. These are the 12 cells in the range! The calculation is always the same style, so for I10 it is: (I8*I7) - I10 and for K10 for example it is: *(K8*K7) - K10. The result is always in the G cell on that row, so for example it is G10 for row 10 and G12 for row 12. Does this make sense? If you look at the formuals I copeis above you will see the patterns...- Hide quoted text - - Show quoted text - Per, it did not work - but thanks so much for your efforts...! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding IF limits with VBA
This appears to work. I tried to get .Find to work in this function but it
refused to cooperate with reliability, so I used a loop. Sorry I took so long, been kinda busy. Function CalcMonth() Dim rng As Range, FoundCell As Range Dim MyRow As Long, ctr As Long MyRow = Application.Caller.Row Set rng = Range("I" & MyRow & ":AE" & MyRow) For Each FoundCell In rng If Not IsEmpty(FoundCell) Then CalcMonth = (FoundCell.Offset(-3, 0) * FoundCell.Offset(-2, 0)) - FoundCell Exit For Else If ctr = 23 Then MsgBox ("No number was found on this row") End If ctr = ctr + 1 Next End Function Mike F "Pelham" wrote in message ... 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. |
Reply |
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 |