Home > OS >  How do I take all values in a column and change their values
How do I take all values in a column and change their values

Time:04-30

There are a lot of questions on how to multiply all values by some other cell or to move all values to another cell based on some value, but what I want is to take, in the example image below:

enter image description here

All the values that I have selected and divide by 2. I do not want another column, I just want to change all those values in the spread sheet and divide them by 2, the values themselves should change.

I have not found an answer for this any where and I sure it is super simple. For example, in:

base_damage_mod selected column, 0.03 would become: 0.015.

The only way I know to do this is manually, and that's a lot of work ...

Whats the easiest way to do this?

CodePudding user response:

The easiest way to do this is by writing a macro, like in the following example:

Sub Divide_by_2()
  For Each c In Selection:
    If c <> "" Then
      c.Value = c.Value / 2
    End If
  Next c
End Sub

In order to launch this, you need to select your cells (no need to copy, or press Ctrl C), and then launch the macro.

As far as the source code is concerned, this is pretty obvious, except for the c <> "" part: I have added this in order to avoid the value 0 being filled in in empty cells.

Is there a way to do this without VBA, without macros?
Yes, there is, but it involves you creating a new column, in there type a formula, then copy the values of that formula into again another column and remove the first two columns, in other words: it's quite Messi :-)

CodePudding user response:

If column C is empty (if not, temporarily insert a column), enter a 2 there next to every used column D item (*). Copy all of column C, and "Paste Special" onto column D using Operation>Divide.

(*) If there are too many items to manually do the "2", copy this formula down column C =IF(ISBLANK(D1),"",2) and it will add them. After this, convert column C from formulas to values by copying it and using "Paste Values" to paste it back. (Special Operations won't work on formulas)

  • Related