PDA

View Full Version : Setting values to a whole range at once



chamster
09-26-2007, 04:13 AM
I'm doing (more or less) the following.

For Each rn In .Range(.Cells(1, 1), .Cells(3, 1))
rn.Value = rn.Value - 1
Next rn


I wonder, however, if it's possible to perform such an assignment to all the range at once. I.e. - perform a drop of every cell's value by one.

Bob Phillips
09-26-2007, 04:44 AM
Is it the number of lines of code that botehr you, or the number of iterations?

rory
09-26-2007, 05:13 AM
Isn't this basically the same question you asked here (http://vbaexpress.com/forum/showthread.php?t=14644)? The answer is pretty much the same. :)

chamster
09-26-2007, 11:05 AM
Is it the number of lines of code that botehr you, or the number of iterations?

Lines of code, i guess. I was hoping for a neat way to express the almost exactly same operation on all cells in a given range. I can always go for-looping but some of the sytax of VB is so nice and humanly canonical. It makes one hope to see such solutions mroe and more.

chamster
09-26-2007, 11:15 AM
Isn't this basically the same question you asked here (http://vbaexpress.com/forum/showthread.php?t=14644)? The answer is pretty much the same. :)
Do you mean something like this?

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

I think i was hoping for something even shorter code-wise. Taking your suggestion, shutting up and moving along, hehe. Thanks.

I'm always on a hunt for smoother coding lines. There should be a section with professionally written code so a newcommer could gaze in awe and learn the better sytax.