Simplify code

[Inkel]

Hi,

I know it lacks experience and optimisation. I know that it could
be shorter but how. Could someone help me ?

The use of it is calculating the need to pay or not and how much,
transportation for employee with their own car.

The value assigned to the argument "region" is the content of a cell
where the employee select in a list the place the worked, and if it is
more
than 120km from the office an amount is payed, looking in a table.

Thanks.

Inkel ;-)

+++++++++++++++++++++++++++++++++++++++++++++++++++

Function vehicule(code, distance)

billet = False

With Sheets("Frais")
A60 = .Range("c7")
A91 = .Range("c8")
A121 = .Range("c9")

FA60 = .Range("e6")
FA91 = .Range("e7")
FA121 = .Range("e8")

B49 = .Range("c11")
B73 = .Range("c12")
B89 = .Range("c13")
B121 = .Range("c14")

FB49 = .Range("e10")
FB73 = .Range("e11")
FB89 = .Range("e12")
FB121 = .Range("e13")
End With

If code = "" Then
vehicule = ""

ElseIf UCase(code) = "A" Then 'Montréal, Trois-Rivières,
Québec & Estrie

If distance < A60 Then
vehicule = FA60
ElseIf distance < A91 Then
vehicule = FA91
ElseIf distance < A121 Then
vehicule = FA121
Else
billet = True
vehicule = autobus(region, billet)
End If

ElseIf UCase(code) = "B" Then 'Reste de la province

If distance < B49 Then
vehicule = FB49
ElseIf distance < B73 Then
vehicule = FB73
ElseIf distance < B89 Then
vehicule = FB89
ElseIf distance < B121 Then
vehicule = FB121
Else
billet = True
vehicule = autobus(region, billet)
End If

ElseIf UCase(code) = "C" Or UCase(code) = "P" Then 'Fournit par
la compagnie
vehicule = " -"

Else
vehicule = ""

End If
'Sheets("Frais").Visible = False
Application.ScreenUpdating = True

End Function

''+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Function autobus(billet, region)

region = Sheets("Frais dépl").Range("k8")

With Sheets("Frais")
r1 = .Range("B21")
r2 = .Range("B22")
r3 = .Range("B23")
r4 = .Range("B24")
r5 = .Range("B25")

P1 = .Range("e21")
P2 = .Range("e22")
P3 = .Range("e23")
P4 = .Range("e24")
P5 = .Range("e25")
End With

If billet = True Then

ElseIf region = r1 Then
autobus = P1

ElseIf region = r2 Then
autobus = P2

ElseIf region = r3 Then
autobus = P3

ElseIf region = r4 Then
autobus = P4

ElseIf region = r5 Then
autobus = P5

ElseIf region = r6 Then
autobus = P6

ElseIf region = r7 Then
autobus = P7

ElseIf region = r8 Then
autobus = P8

ElseIf region = r9 Then
autobus = P9

End If

End Function

 

[Jacob Skaria]

Dear Inkel please refer your previous post..

If this post helps click Yes

 

[Bob Phillips]

Does it work, is it fast enough? If so, it ain't broke, so why bother
messing with it?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hi,

I know it lacks experience and optimisation. I know that it could
be shorter but how. Could someone help me ?

The use of it is calculating the need to pay or not and how much,
transportation for employee with their own car.

The value assigned to the argument "region" is the content of a cell
where the employee select in a list the place the worked, and if it is
more
than 120km from the office an amount is payed, looking in a table.

Thanks.

Inkel ;-)

+++++++++++++++++++++++++++++++++++++++++++++++++++

Function vehicule(code, distance)

billet = False

With Sheets("Frais")
A60 = .Range("c7")
A91 = .Range("c8")
A121 = .Range("c9")

FA60 = .Range("e6")
FA91 = .Range("e7")
FA121 = .Range("e8")

B49 = .Range("c11")
B73 = .Range("c12")
B89 = .Range("c13")
B121 = .Range("c14")

FB49 = .Range("e10")
FB73 = .Range("e11")
FB89 = .Range("e12")
FB121 = .Range("e13")
End With

If code = "" Then
vehicule = ""

ElseIf UCase(code) = "A" Then 'Montréal, Trois-Rivières,
Québec & Estrie

If distance < A60 Then
vehicule = FA60
ElseIf distance < A91 Then
vehicule = FA91
ElseIf distance < A121 Then
vehicule = FA121
Else
billet = True
vehicule = autobus(region, billet)
End If

ElseIf UCase(code) = "B" Then 'Reste de la province

If distance < B49 Then
vehicule = FB49
ElseIf distance < B73 Then
vehicule = FB73
ElseIf distance < B89 Then
vehicule = FB89
ElseIf distance < B121 Then
vehicule = FB121
Else
billet = True
vehicule = autobus(region, billet)
End If

ElseIf UCase(code) = "C" Or UCase(code) = "P" Then 'Fournit par
la compagnie
vehicule = " -"

Else
vehicule = ""

End If
'Sheets("Frais").Visible = False
Application.ScreenUpdating = True

End Function

''+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Function autobus(billet, region)

region = Sheets("Frais dépl").Range("k8")

With Sheets("Frais")
r1 = .Range("B21")
r2 = .Range("B22")
r3 = .Range("B23")
r4 = .Range("B24")
r5 = .Range("B25")

P1 = .Range("e21")
P2 = .Range("e22")
P3 = .Range("e23")
P4 = .Range("e24")
P5 = .Range("e25")
End With

If billet = True Then

ElseIf region = r1 Then
autobus = P1

ElseIf region = r2 Then
autobus = P2

ElseIf region = r3 Then
autobus = P3

ElseIf region = r4 Then
autobus = P4

ElseIf region = r5 Then
autobus = P5

ElseIf region = r6 Then
autobus = P6

ElseIf region = r7 Then
autobus = P7

ElseIf region = r8 Then
autobus = P8

ElseIf region = r9 Then
autobus = P9

End If

End Function

__________ Information from ESET Smart Security, version of virus signature
database 3832 (20090206) __________

The message was checked by ESET Smart Security.

http://www.eset.com





__________ Information from ESET Smart Security, version of virus signature database 3832 (20090206) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Construk]

Because i'm sure there is ways to do it better and i'm looking to learn from
more experienced people out there.

Sure, wooden wheels were great, but what about skidoos, planes or rockets ?

Inkel