Changing the signs of values in a column by multiplying it by -1 in VBA

Question

i.e. column Q in a worksheet where values start in Q3 and they can at different rows in the column. I want to take the positive values in the column and flip them to negative values and take negative values and flip them to positive. I was thinking of using an If statement here.

sub macro4()

with thisworkbook
     Set uftrad = .Worksheets("Output - Trad NP reformatted")
End With

uftrad.Activate
Range("q3").Activate

For i = 0 To 64

If ActiveCell.Value <> 0 Then
ActiveCell.Value * - 1
Else
    ActiveCell.Offset(i, 0).Select
End If

End Sub

The above is not working (and I am sure the reasons would be glaringly obvious to most of you here) can you guys help me?

Multiplying 0 by -1 keeps it at 0, so I don't see the point of the check. Also, I think that you could benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/q/10714251/4996248). — John Coleman, Sep 18 '18 at 17:51
The for-loop is missing a "next i". I would also not use active cell but Cells(i,j).Value = Cells(i,j)*-1 where j is the column number (can be fixed value) — Jan, Sep 18 '18 at 17:51

Scott Craner · Answer 1 · 2018-09-18T18:09:14.307

We can use Evaluate and eliminate the need for a loop:

Sub macro4()
    With ThisWorkbook.Worksheets("Output - Trad NP reformatted")
        With .Range("Q3", .Cells(.Rows.Count, "Q").End(xlUp))
            .Value = .Parent.Evaluate("INDEX(-1 * " & .Address(0, 0) & ",)")
        End With
    End With
End Sub

If you want to loop, loop a variant array.

Load the values into an array do your math and put the array values back:

Sub macro4()
    With ThisWorkbook.Worksheets("Output - Trad NP reformatted")
        With .Range("Q3", .Cells(.Rows.Count, "Q").End(xlUp))
            Dim rng As Variant
            rng = .Value
            Dim i As Long
            For i = LBound(rng, 1) To UBound(rng, 1)
                rng(i, 1) = -1 * rng(i, 1)
            Next i
            .Value = rng
        End With
    End With
End Sub

score 3 · Answer 2 · answered Sep 18 '18 at 17:50

3

You can just loop through a range and monkey with the cell in each iteration:

Sub macro64()
    Dim rngCell as Range

    For Each rngCell in Range("Q3:Q67").Cells
        rngCell.Value = -1 * rngCell.Value
    Next rngCell
End Sub

This way you aren't having to worry about Activating or Selecting a cell. And you don't have to monkey with offset and all that. Just a quick loop and you are done.

answered Sep 18 '18 at 17:50

JNevill

46,980
4
38
63

@HaiderImam please mark the one that helped you the most, or the one you used as correct by clicking the check mark by the answer that you choose. It will remove the question from the unanswered Queue and reward the person who gave the correct answer. Do not go by the votes, but by which you see as the correct answer. It is something only the person posing the question can do. You can also only select one. – Scott Craner Sep 18 '18 at 18:38

score 3 · Answer 3 · answered Sep 18 '18 at 18:13

3

My 2 cents.

Sub ReverseValues()

    Dim rng As Range

    Set rng = Range("Q3:Q" & Cells(Rows.Count, "Q").End(xlUp).Row)
    rng = Evaluate(rng.Address & "*-1")


End Sub

answered Sep 18 '18 at 18:13

Davesexcel

6,896
2
27
42

1

I guess I did not need the `INDEX` part, habit i guess. – Scott Craner Sep 18 '18 at 18:15

score 1 · Answer 4 · answered Sep 18 '18 at 18:06

1

If you have a spare column you could do it like that

Range("R3:R67").Formula = "=-RC[-1]"
Range("Q3:Q67").value = Range("R3:R67").value
Range("R3:R67").clear

answered Sep 18 '18 at 18:06

Storax

11,158
3
16
33

Changing the signs of values in a column by multiplying it by -1 in VBA

4 Answers4