| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
excel VBA help
I have 31 cases. from 1 to 31 and i need to optimize the following code. how
do i do it either using for stmt or other loops 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: |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel VBA help
On Jan 29, 1:52*am, "Bob Phillips" wrote:
* * * * With Sheets("FTP") * * * * * * .Cells(i, 3).Resize(, x).Value = 8 * * * * * * .Cells(i, 3 + x) = estimate - (8 * x) * * * * End With Wow! If you wouldn't mind doing a little tutorial, can you explain this usage of the Resize method. When I look at Help, it looks nothing like what you have here. (I have Office Excel 2003 with VB 6.3.) Of course, I can infer from the OP's example what your solution "must" do. I 'spose Cells(i,3).Resize(,x) specifies a list object. But I don't know anything about list objects. I don't recall reading it in Walkenbach's book. That doesn't mean it's not there; it simply means I have no memory of it. And as usual, his index is so poor that I cannot find a pointer to a discussion, if it exists. A pointer to a good discussion of list objects would be helpful. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
excel VBA help
I don't think it is doing anything out of the norm.
From the OPs question I deduced that if x is 1, one cell gets the value, if 2 2 cells, 3 then 3 cells, etc. So I just started at the first cell, ..Cells(i, 3) and resized that by x columns (,3), and directly assigned the value 8 to that resized RANGE object. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "joeu2004" wrote in message ... On Jan 29, 1:52 am, "Bob Phillips" wrote: With Sheets("FTP") .Cells(i, 3).Resize(, x).Value = 8 .Cells(i, 3 + x) = estimate - (8 * x) End With Wow! If you wouldn't mind doing a little tutorial, can you explain this usage of the Resize method. When I look at Help, it looks nothing like what you have here. (I have Office Excel 2003 with VB 6.3.) Of course, I can infer from the OP's example what your solution "must" do. I 'spose Cells(i,3).Resize(,x) specifies a list object. But I don't know anything about list objects. I don't recall reading it in Walkenbach's book. That doesn't mean it's not there; it simply means I have no memory of it. And as usual, his index is so poor that I cannot find a pointer to a discussion, if it exists. A pointer to a good discussion of list objects would be helpful. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
