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