Performing an operation on several cells at once

[chamster]

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]

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]

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]

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]

I might also think it is hardly worth it, but here is one way anyway

[vba]

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
[/vba]

[Norie]

Along the lines of xld's code.
[vba]
With Range("B1:B10")
.Formula = "=A1+1"
.Value = .Value
End With
[/vba]

[chamster]

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]

Along the lines of xld's code.
[vba]
.Value = .Value
[/vba]

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

[Bob Phillips]

Yes it does.