| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplify code
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simplify code
Dear Inkel please refer your previous post..
If this post helps click Yes --------------- Jacob Skaria "Inkel" wrote: 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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simplify code
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) "Inkel" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simplify code
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 "Bob Phillips" wrote: 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) "Inkel" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Simplify this formula | Excel Worksheet Functions | |||
| Simplify Formula | Excel Worksheet Functions | |||
| Simplify this macro | Excel Discussion (Misc queries) | |||
| Need to simplify code | Excel Discussion (Misc queries) | |||
| simplify code | Excel Discussion (Misc queries) | |||
Linear Mode
