Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Excel code optimization
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 45
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 2,059
Default 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.
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 59
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,593
Default 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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 10,593
Default 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.


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
  
Posts: 45
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting Se0 Guy Excel Worksheet Functions 0 March 8th 07 04:08 AM
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting Se0 Guy Excel Discussion (Misc queries) 0 March 8th 07 04:08 AM
Seo , Search Engine Optimizer , Seo Search engine Optimization , search engine optimization services, SEO Consulting Se0 Guy Setting up and Configuration of Excel 0 March 8th 07 04:08 AM
Advanced MS Excel charts with data optimization cbalster Excel Discussion (Misc queries) 0 May 26th 06 10:44 PM
optimization plug-in for Excel 2003. AG Excel Worksheet Functions 2 October 5th 05 06:59 AM


All times are GMT +1. The time now is 03:59 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement