ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   Excel code optimization (https://www.excelbanter.com/excel-discussion-misc-queries/174787-excel-code-optimization.html)

deepika :excel help[_2_]
Excel code optimization
 
How do i optimize the following code wit 31 cases
here i is the row index (dynamic) and in everycase the estimate gets
subtracted by multiples of 8. so for case 31 we need to have 8*31= 248 as a
subtraction to the estimate. This is quite urgent and i do not know prog at
all. Kindly help
Select Case (x)

Case 1:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = estimate - 8
Case 2:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = estimate - 16
Case 3:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) =estimate - 24

Case 4:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) = 8
Sheets("FTP").Cells(i, 7) = estimate - 32
Case 5:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) = 8
Sheets("FTP").Cells(i, 7) = 8
Sheets("FTP").Cells(i, 8) = estimate - 40

Case 6:
.....
case 31 :
End Select

joeu2004
Excel code optimization
 
On Jan 29, 3:43*am, deepika :excel help
wrote:
How do i optimize the following code wit 31 cases

That depends on your definition of "optimize". In a sense, you have
the most "optimized" coding insofar as it executes the fewest number
of statements for any given value of "x". That is, if the Select
statement is implemented in the most efficient manner, namely a branch
table instead of a sequence of "if" statements. I don't know if it
is.

But if by "optimized", you mean the fewest written statements, I think
the following would work for you. In place of the Select statement:

for j = 3 to 3+x-1
Sheets("FTP").Cells(i, j) = 8
next j
Sheets("FTP").Cells(i, j) = estimate - 8*x

(Note that the variable "j" is equal to 3+x when the loop exits
normally.)

The execution time might be slightly slower because of the mechanics
of the For statement. But I doubt that you would notice the
difference unless you execute these statements a great many times.

By the way, in case "x" might be outside the range of 1-to-31, the
following addition might be prudent in order to have exactly the same
semantics of the Select statement that you wrote:

if 1<=x and x<=31 then
for j = 3 to 3+x-1
Sheets("FTP").Cells(i, j) = 8
next j
Sheets("FTP").Cells(i, j) = estimate - 8*x
end if

HTH.

Matt Richardson
Excel code optimization
 
Rather than use the select case as you have below, you could try this
bit of code instead, which I think does exactly the same job:-

Dim thisLoop as Integer

For thisLoop = 1 To x
Sheets("FTP").Cells(i, thisLoop + 2) = 8
Next

Sheets("FTP").Cells(i, x + 3) = estimate - (x * 8)


Hope this helps,
Matt Richardson
http://teachr.blogspot.com


On Jan 29, 11:43 am, deepika :excel help
wrote:
How do i optimize the following code wit 31 cases
here i is the row index (dynamic) and in everycase the estimate gets
subtracted by multiples of 8. so for case 31 we need to have 8*31= 248 as a
subtraction to the estimate. This is quite urgent and i do not know prog at
all. Kindly help
Select Case (x)

Case 1:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = estimate - 8
Case 2:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = estimate - 16
Case 3:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) =estimate - 24

Case 4:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) = 8
Sheets("FTP").Cells(i, 7) = estimate - 32
Case 5:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) = 8
Sheets("FTP").Cells(i, 7) = 8
Sheets("FTP").Cells(i, 8) = estimate - 40

Case 6:
....
case 31 :
End Select

Bob Phillips
Excel code optimization
 
Why are you re-posting, why not just read replies to the earlier duplicate
question?

--
---
HTH

Bob


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



"deepika :excel help" wrote in
message ...
How do i optimize the following code wit 31 cases
here i is the row index (dynamic) and in everycase the estimate gets
subtracted by multiples of 8. so for case 31 we need to have 8*31= 248 as
a
subtraction to the estimate. This is quite urgent and i do not know prog
at
all. Kindly help
Select Case (x)

Case 1:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = estimate - 8
Case 2:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = estimate - 16
Case 3:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) =estimate - 24

Case 4:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) = 8
Sheets("FTP").Cells(i, 7) = estimate - 32
Case 5:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) = 8
Sheets("FTP").Cells(i, 7) = 8
Sheets("FTP").Cells(i, 8) = estimate - 40

Case 6:
....
case 31 :
End Select



Bob Phillips
Excel code optimization
 
See my response to the previous thread.

--
---
HTH

Bob


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



"joeu2004" wrote in message
...
On Jan 29, 3:43 am, deepika :excel help
wrote:
How do i optimize the following code wit 31 cases

That depends on your definition of "optimize". In a sense, you have
the most "optimized" coding insofar as it executes the fewest number
of statements for any given value of "x". That is, if the Select
statement is implemented in the most efficient manner, namely a branch
table instead of a sequence of "if" statements. I don't know if it
is.

But if by "optimized", you mean the fewest written statements, I think
the following would work for you. In place of the Select statement:

for j = 3 to 3+x-1
Sheets("FTP").Cells(i, j) = 8
next j
Sheets("FTP").Cells(i, j) = estimate - 8*x

(Note that the variable "j" is equal to 3+x when the loop exits
normally.)

The execution time might be slightly slower because of the mechanics
of the For statement. But I doubt that you would notice the
difference unless you execute these statements a great many times.

By the way, in case "x" might be outside the range of 1-to-31, the
following addition might be prudent in order to have exactly the same
semantics of the Select statement that you wrote:

if 1<=x and x<=31 then
for j = 3 to 3+x-1
Sheets("FTP").Cells(i, j) = 8
next j
Sheets("FTP").Cells(i, j) = estimate - 8*x
end if

HTH.



deepika :excel help[_2_]
Excel code optimization
 
Thank you very much.. i will ck this code

"Matt Richardson" wrote:

Rather than use the select case as you have below, you could try this
bit of code instead, which I think does exactly the same job:-

Dim thisLoop as Integer

For thisLoop = 1 To x
Sheets("FTP").Cells(i, thisLoop + 2) = 8
Next

Sheets("FTP").Cells(i, x + 3) = estimate - (x * 8)


Hope this helps,
Matt Richardson
http://teachr.blogspot.com


On Jan 29, 11:43 am, deepika :excel help
wrote:
How do i optimize the following code wit 31 cases
here i is the row index (dynamic) and in everycase the estimate gets
subtracted by multiples of 8. so for case 31 we need to have 8*31= 248 as a
subtraction to the estimate. This is quite urgent and i do not know prog at
all. Kindly help
Select Case (x)

Case 1:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = estimate - 8
Case 2:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = estimate - 16
Case 3:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) =estimate - 24

Case 4:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) = 8
Sheets("FTP").Cells(i, 7) = estimate - 32
Case 5:
Sheets("FTP").Cells(i, 3) = 8
Sheets("FTP").Cells(i, 4) = 8
Sheets("FTP").Cells(i, 5) = 8
Sheets("FTP").Cells(i, 6) = 8
Sheets("FTP").Cells(i, 7) = 8
Sheets("FTP").Cells(i, 8) = estimate - 40

Case 6:
....
case 31 :
End Select


All times are GMT +1. The time now is 02:32 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com