Excel code optimization

  • Thread starter deepika :excel help
  • Start date
D

deepika :excel help

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
 
J

joeu2004

How do i optimize the following code wit 31 cases
Click to expand...

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.
 
M

Matt Richardson

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
 
B

Bob Phillips

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)
 
B

Bob Phillips

See my response to the previous thread.

--
---
HTH

Bob


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



How do i optimize the following code wit 31 cases
Click to expand...

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.
 
D

deepika :excel help

Thank you very much.. i will ck this code

Matt Richardson said:
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
Click to expand...
 
Top