preference question

[Gary Keramidas]

just wondering what most of you professional coders would use:
this vba routine to copy data to a summary sheet
----------------------------------------------------------------------
For c = 4 To 15

Sheets(c).Range("C6").Copy
Sheets("Monthly Totals").Select
Range("C5").Offset(c2, 0).PasteSpecial xlPasteValues,
xlPasteSpecialOperationAdd

Sheets(c).Range("D6").Copy
Sheets("Monthly Totals").Select
Range("D5").Offset(c2, 0).PasteSpecial xlPasteValues,
xlPasteSpecialOperationAdd


Sheets(c).Range("E6").Copy
Sheets("Monthly Totals").Select
Range("E5").Offset(c2, 0).PasteSpecial xlPasteValues,
xlPasteSpecialOperationAdd

c2 = c2 + 1

Next c

 

[STEVE BELL]

I am no expert, but you can do it more easily and not require selecting.
This will run faster...

Sheets("Monthly Totals").Range("C5").Offset(c2,
0).value=Sheets(c).Range("C6").Value

 

[Gary Keramidas]

thanks, steve, that worked (had to change c6 to c5 in your example, but it
gave me what i needed. i tried to combine the steps and didn't have any
luck, so i just did it that way because it worked.

now, back to the question, would you use a formula in each cell, or the vba
code to create the report?

 

[STEVE BELL]

Gary,

You're very welcome!
As far as combining the steps - you need to find a consistant relationship
between the cells to get there. Like your example shows you copying from C,
D, & E
so you could loop through them.

Range("C6")=Cells(6,3)

This will move C6, D6, E6 to the offset for C5, D5, & E5

For x = 3 to 5
Sheets("Monthly Totals").Cells(5+c2,x).value=Sheets(c).Cells(6,x).Value
Next


My preference??? Can't really say. Depends on too many things:
1. Do I want a spreadsheet solution or a code solution (personal
preference).
Code can be faster and easier, especially with multiple if
statements
2. Do I want to clutter my worksheet with formulas.
In your case you have only a small number of cells and formulas
work well.
But code can easily figure out where to put the values.
A lot of formulas will enlarge your workbook and slow it down
(talking 1000's)
3. Formulas don't put in values - they put in formulas.
Code can do either formulas or values.
4. With code you automate.

Lots of time it depends on my mode and whether I want to bother with code.
Sometimes it depends on whom I am building this for - some people would
rather not have code.

And the list goes on, and on, and on.

My experience is that if you get 12 people in a room - they can come up with
at least 25 ways to do the same thing (I have seen multiple versions of code
to do the same thing). Some are better than others - but they all work...

keep on Exceling

 

[Guest]

For something like this I would just stick with the formulas. 36 formulas is
not enough to degrade your performance and the formulas are a whole lot
easier to debug than code.