Help with IF... Then... Else statement

[Nick VL]

I originally was going to do this just as a standard (albeit long) function
in Excel, but quickly had more nested levels then allowed. So my hope is
that I can in VBA produce a custom function that will do all of the work and
be compatible with 2003 xls format. Please let me know if this is possible
and if there is a better approach to this. Below is the VBA that I have so
far, that obviously does not work Im not sure how to use/insert the excel IF
statements. Thanks for ANY help.


Public Function GroundCover()
'Create Variables to hold numbers
Dim input1 As Double
Dim input2 As Double
Dim input3 As Double

'Fill the variables with input from the worksheet
input1 = Worksheet.cels(27, 2)
input2 = Worksheet.Cells(27, 3)
input3 = Worksheet.Cells(27, 4)
' Determine if input1 = flats
If input1 = "Flats" Then

'Use IF statement to check input2 and multiply input3 by coresponding
number
Output=(IF(input2=4,input3*0.1406, IF(input2=6,input3*0.0625,
IF(input2=8,input3*0.0351, IF(input2=9,input3*0.0278,
IF(input2=10,input3*0.0225, IF(input2=12,input3*0.0156,
IF(input2=18,input3*0.0087, IF(input2=24,input3*0.0039,
IF(input2=30,input3*0.0025,"0"))))))))))

Else
'Use second IF statement to check input1 and multiply input3 by
coresponding number
Output=(IF(input2=4,input3*9, IF(input2=6,input3*4,
IF(input2=8,input3*2.25, IF(input2=9,input3*1.78, IF(input2=10,input3*1.44,
IF(input2=12,input3, IF(input2=16,input3*0.56, IF(input2=18,input3*0.45,
IF(input2=24,input3*0.25, IF(input2=30,input3*0.16,
IF(input2=36,input3*0.1111, IF(input2=48,input3*0.0625,
IF(input2=72,input3*0.0278,"0")))))))))))))

End If


End Function

 

[Nick VL]

Sorry, the word wrap here kinda makes it hard(er) to read/understand...

 

[Per Jessen]

Hi

Look at this: (I will leave it to you to rewrite your second IF
statement.)

Public Function GroundCover(Input1 As String, Input2 As Integer,
Input3 As Double)

' Determine if input1 = flats
If Input1 = "Flats" Then
'Use Select Case input2 and multiply input3 by coresponding Number
Select Case Input2
Case Is = 4
Output = Input3 * 0.1406
Case Is = 6
Output = Input3 * 0.0625
Case Is = 8
Output = Input3 * 0.0351
Case Is = 9
Output = Input3 * 0.0278
Case Is = 10
Output = Input3 * 0.0225
Case Is = 12
Output = Input3 * 0.0156
Case Is = 18
Output = Input3 * 0.0087
Case Is = 24
Output = Input3 * 0.0039
Case Is = 30
Output = Input3 * 0.0025
Case Else
Output = 0
End Select
Else
'Use second IF statement to check input1 and multiply input3 by
coresponding Number
'Output=(IF(input2=4,input3*9, IF(input2=6,input3*4,
'IF(input2=8,input3*2.25, IF(input2=9,input3*1.78,
IF(input2=10,input3*1.44,
'IF(input2=12,input3, IF(input2=16,input3*0.56,
IF(input2=18,input3*0.45,
'IF(input2=24,input3*0.25, IF(input2=30,input3*0.16,
'IF(input2=36,input3*0.1111, IF(input2=48,input3*0.0625,
'IF(input2=72,input3*0.0278,"0")))))))))))))
End If
GroundCover = Output
End Function

Regards,
Per

 

[JMB]

You could use a lookup table to get around the nesting limit. If you set up
a table like this that contain your multiplication factors for each value of
Input 2 (lets say this table is in Sheet3!A1:C13):

4 0.1406 9
6 0.0625 4
8 0.0351 2.25
9 0.0278 1.78
10 0.0225 1.44
12 0.0156 1
16 0 0.56
18 0.0087 0.45
24 0.0039 0.25
30 0.0025 0.16
36 0 0.1111
48 0 0.0625
72 0 0.0278

and Input1 is in B27, Input2 is C27, and input3 is D27, I think this will do
what you need:

=IF(ISNUMBER(MATCH(C27,Sheet3!A1:A13,0)),D27*VLOOKUP(C27,Sheet3!A1:C13,IF(B27="Flats",2,3),0),0)

 

[Nick VL]

Thanks both of you.

You could use a lookup table to get around the nesting limit. If you set up
a table like this that contain your multiplication factors for each value of
Input 2 (lets say this table is in Sheet3!A1:C13):

4 0.1406 9
6 0.0625 4
8 0.0351 2.25
9 0.0278 1.78
10 0.0225 1.44
12 0.0156 1
16 0 0.56
18 0.0087 0.45
24 0.0039 0.25
30 0.0025 0.16
36 0 0.1111
48 0 0.0625
72 0 0.0278

and Input1 is in B27, Input2 is C27, and input3 is D27, I think this will do
what you need:

=IF(ISNUMBER(MATCH(C27,Sheet3!A1:A13,0)),D27*VLOOKUP(C27,Sheet3!A1:C13,IF(B27="Flats",2,3),0),0)

 

[Nick VL]

Thank you. This seems to work. Here's the final function if anyone cares:

Public Function GroundCover(Input1 As String, Input2 As Integer, Input3 As
Double)

' Determine if input1 = flats

If Input1 = "Flats" Then

'Use Select Case input2 and multiply
'input3 by coresponding Number

Select Case Input2
Case Is = 4
Output = Input3 * 0.1406
Case Is = 6
Output = Input3 * 0.0625
Case Is = 8
Output = Input3 * 0.0351
Case Is = 9
Output = Input3 * 0.0278
Case Is = 10
Output = Input3 * 0.0225
Case Is = 12
Output = Input3 * 0.0156
Case Is = 18
Output = Input3 * 0.0087
Case Is = 24
Output = Input3 * 0.0039
Case Is = 30
Output = Input3 * 0.0025
Case Else
Output = "-"
End Select
Else

'Use second IF statement to check input1
'and multiply input3 by coresponding Number

If Not Input1 = "Flats" Then

'Use Select Case input2 and multiply input3
'by coresponding Number

Select Case Input2
Case Is = 4
Output = Input3 * 9
Case Is = 6
Output = Input3 * 4
Case Is = 8
Output = Input3 * 2.25
Case Is = 9
Output = Input3 * 1.78
Case Is = 10
Output = Input3 * 1.44
Case Is = 12
Output = Input3 * 1
Case Is = 16
Output = Input3 * 0.56
Case Is = 18
Output = Input3 * 0.45
Case Is = 24
Output = Input3 * 0.25
Case Is = 30
Output = Input3 * 0.16
Case Is = 36
Output = Input3 * 0.1111
Case Is = 48
Output = Input3 * 0.0625
Case Is = 72
Output = Input3 * 0.0278
Case Else
Output = "-"
End Select

End If
GroundCover = Output
End If
GroundCover = Output

End Function

 

[RDFischer]

Nick VL,

Within VBA is a statement titled Select Case. For complicated sequences
such as you want it is easier to construct and document. I rewrote your
function as a macro you can run from within an Excel worksheet. It looks
bigger and more complicated because it spreads the function out over many
lines, however, that makes it much easier to write and debug.

For an Excel worksheet a subroutine forms the basis for a macro. I
named it the same as you named your function. The way it works is for the
user to first select a cell where they answer is to be placed. You then
select the run macro entry from the tools\macro menu item. Then run macro
GroundCover. The macro routine uses the three cells to fetch the data then
calculates the desired result and finally places that result in the cell you
select initially.

You can select any cell on the sheet.

If your not familiar with the Select Case statement you can go to the
Visual Basic window and use help to get a description. The short answer is
that you enter a value or expression to be evaluated after the Select Case
statement. Following that are a series of case descriptions in the form of
expressions. After each case expression is one or more statements to be
executed if the condition of the case is met.

For the routine shown, each input value is the case being selected. The
statement after each case option defines the calculation of your result.
All other cases are then ignored and the procedure jumps to the End Select.

Two sets of Select Case are used, one for the situation where "Flats" is
entered and a second for any other situation. It is feasible and reasonable
to use a Select Case for the cell contents that has the word "Flats" also in
which case you could have more than two options.

To use this in your copy of Excel, copy the entire subroutine into the
worksheet VB code window for your worksheet. When you select the macro
window on the spreadsheet, the macro GroundCover will show up ready for use.

Hope this longwinded answer helps.

RDF


Public Sub GroundCover()
'Visual Basic creates variables directly from code
'Fill the variables with input from the worksheet
With Worksheets("Sheet1") ' when doing multiple actions on a
worksheet, use the With .... End Width construct
input1 = .Cells(27, 2) ' use .cells function to access
worksheet data
input2 = .Cells(27, 3)
input3 = .Cells(27, 4)
' Determine if input1 = flats
If input1 = "Flats" Then

' use the Select Case construct instead of the nested if.. VBA code
options are much richer than EXCEL functions
Select Case input2
Case Is = 4
Ground_cover = input3 * 0.1406
Case Is = 6
Ground_cover = input3 * 0.0625
Case Is = 8
Ground_cover = input3 * 0.0351
Case Is = 9
Ground_cover = input3 * 0.0278
Case Is = 10
Ground_cover = input3 * 0.0225
Case Is = 12
Ground_cover = input3 * 0.0156
Case Is = 18
Ground_cover = input3 * 0.0087
Case Is = 24
Ground_cover = input3 * 0.0025
Case Is = 30
Ground_cover = input3 * 0.0025
Case Else
Ground_cover = 0 'probably should be numeric instead of
text... was "0"
End Select

Else
'Use second IF statement to check input1 and multiply input3 by
'coresponding Number
Select Case input2
Case Is = 4
Ground_cover = input3 * 9
Case Is = 6
Ground_cover = input3 * 4
Case Is = 8
Ground_cover = input3 * 2.25
Case Is = 9
Ground_cover = input3 * 1.78
Case Is = 10
Ground_cover = input3 * 1.44
Case Is = 12
Ground_cover = input3
Case Is = 16
Ground_cover = input3 * 0.56
Case Is = 18
Ground_cover = input3 * 0.45
Case Is = 24
Ground_cover = input3 * 0.25
Case Is = 30
Ground_cover = input3 * 0.16
Case Is = 36
Ground_cover = input3 * 0.1111
Case Is = 48
Ground_cover = input3 * 0.0625
Case Is = 72
Ground_cover = input3 * 0.0278
Case Else
Ground_cover = 0 'probably should be numeric instead of
text... was "0"
End Select

End If

' return result to the selected cell
Selection.Value = Ground_cover
End With
End Sub

 

[Nick VL]

Thank you. This is similar to the function I ended up building with help
from Per Jessen above. I will keep this in mind for a possible macro in a
different area of the spreadsheet.