Modify range of cells at once...

[Andrey Kazak]

Greetings, dear Experts!

I want to select range and run such Command/Tool that modifies current
values of the range by simple formula (+, -, *, /) and pastes result values
instead the original ones.
Is this possible by the standard means of Excel?

Thank you in advance for propmt reply...

 

[Lars-Åke Aspelin]

Greetings, dear Experts!

I want to select range and run such Command/Tool that modifies current
values of the range by simple formula (+, -, *, /) and pastes result values
instead the original ones.
Is this possible by the standard means of Excel?

Thank you in advance for propmt reply...

If you want to Add, Subtract, Multiply, Divide
the content of all cells in a range with a constant value you can do
like this:

1) Put the constant, e.g. 7, in an unused cell.
2) Right click on the cell and chose "Copy"
3) Select the range
4) Right click and chose "Paste Special..."
5) In the dialog, chose the wanted Operation, e.g. Add
6) Click on OK
7) All values in the range have now been changed and you can delete
the constant

Hope this helps / Lars-Åke

 

[FSt1]

hi
not sure if i understand but if you want to add 2 to a number of cells in a
range (or mutiply, subtract, divide), enter a 2 (or whatever number you need)
into a cell off to the side and copy it. then use pasted special>Add (or
mutiply, subtract, divide).

regards
FSt1

 

[Andrey Kazak]

Yes. This is exacly what I want. But is there any way to do it without
utilizing of a spare cell?

 

[FSt1]

hi
no, not that i know of. excel need a way to know what to add( or mutiply,
subtract, divide) to all the cells.
and in my last post, i seem to have forgotten to mention to high light all
the cells first. then do the copy/paste special. the operation will only
effect high lighted cells.

Regards
FSt1

 

[Gord Dibben]

Use a macro if you don't want to employ a spare cell.

Sub add_two()
For Each cell In Selection
With cell
.Value = .Value + 2
End With
Next
End Sub

But this would not be "standard means"


Gord Dibben MS Excel MVP

 

[gls858]

You can delete the contents of the cell after you do the copy and paste.
It doesn't need to stay there.

gls858