PDA

View Full Version : Performing an operation on several cells at once



chamster
08-30-2007, 06:01 AM
I know that i can loop through all 10 cells in a column and populate the column right next to it by
.Range("B" & cnt).Value = .Range("A" & cnt).Value + 1
but i can't stop wondering if i can perform a range-wise operation.

I've tried with
.Range("B" & cnt).Value = .Range("A" & cnt).Value + 1
but that didn't work. I've been laborating with "For Each" but that didn't worked out well. Any suggestions?

rory
08-30-2007, 06:11 AM
Stick with what you've got. The alternative would be to use a copy + paste special -> Add but I can't see the point.

chamster
08-30-2007, 06:17 AM
I was reasoning in the abouts of possible effecitivity of the code as well as the elegance of the expression. It seems somewhat unsophisticated to go cell-by-cell recomputing the value...

rory
08-30-2007, 06:23 AM
If you are only doing 10 cells , efficiency isn't much of an issue. You could grab the values of the entire column A range into a variant, loop through adding 1 to each value and then write that out in one go to the column B range but you won't notice the difference in speed with so few items.
You can't just add 1 to an array of values without looping through them.

Bob Phillips
08-30-2007, 06:23 AM
I might also think it is hardly worth it, but here is one way anyway



With ActiveSheet
.Range("A1").Resize(cnt).Copy .Range("B1")
.Range("B1").Offset(cnt).Value = 1
.Range("B1").Offset(cnt).Copy
.Range("B1").Resize(cnt).PasteSpecial Paste:=xlPasteAll, _
Operation:=xlAdd
.Range("B1").Offset(cnt).ClearContents
End With

Norie
08-30-2007, 06:55 AM
Along the lines of xld's code.

With Range("B1:B10")
.Formula = "=A1+1"
.Value = .Value
End With

chamster
08-30-2007, 07:09 AM
Oh, but it is worth it. Today i'm laborating on 10 cells. Tomorrow, perhaps, i'll be doing the same on 10 000. One might as well do it properly from the start. Plus - one should never pass an oportunity to learn something new!

chamster
08-30-2007, 07:13 AM
Along the lines of xld's code.

.Value = .Value


This was surprising. What effect does it have if any? Does it actually REMOVE the formula previously entered?

Bob Phillips
08-30-2007, 07:27 AM
Yes it does.