Avoiding IF limits with VBA

P

Pelham

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!
 
Y

ytayta555

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
 
P

Pelham

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
Click to expand...

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...
 
Y

ytayta555

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.
Click to expand...


...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
 
M

Mike Fogleman

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



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!
 
P

Per Jessen

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
 
P

Pelham

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.
Click to expand...

...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
Click to expand...

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.
 
P

Pelham

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


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!
Click to expand...

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.
 
P

Pelham

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








- Show quoted text -
Click to expand...

Per, it did not work - but thanks so much for your efforts...!
 
M

Mike Fogleman

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
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


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!
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

range method failed 2
Timesheet Date Populating Problem 8
UDF ...VBA Formula built ...please help 22
VBA for Lookup to other Columns 4
Help with VBA 2
VBA Code Help? 1
Run-Time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from 0
Run-Time error '-2147417848 (80010108)': Automation error The object invoked has disconnected from 0

Top