Negative to Positive

[stumps]

Hi Forum

I would like to change a few cells that appear as negative numbers to positive numbers.

 

[stumps]

Or simply =ABS(A2) .

But IMHO, "stumps's" request is unclear. For starters, does column A (or whatever) contain constants or formulas? Does "stump" merely want to change how the values "appear"?

Spreadsheet 1 has column A who's cells contain formulas, the cells totals add up and are placed in a single cell which is a negative number
i.e. cells A3:A99 all have formulas with negative totals, that is placed in cell A100 - formula in cell A100 : = sum(A3:A99)

In Spreadsheet 2 the total of cell A100 is placed into cell B1 : =Spreadsheet 1!A100 - would like to have this number to change into a positive

 

[MARK858]

Since I am learning VBA, I was trying to make a macro to convert all the negative number into positive numbers but due to some reason it doesn't work. The VBA seems to reject my IF statements.

Experts' assistance required.

Sub positive()

Dim r As Range
Dim c As Integer


Set r = Selection


[COLOR=#ff0000]    For Each c In r[/COLOR]
[COLOR=#ff0000]        If c.value < 0[/COLOR]
        Then c.value =  c * -1
        Else
        x
    End If
    Next


End Sub

Why do you even need the Else statement as you are already testing for only cells with the negative?

Sub positive()

Dim r As Range, c As Range

Set r = Selection

For Each c In r
If c.Value < 0 Then c.Value = c.Value * -1
Next

End Sub

Back to the question...

Spreadsheet 1 has column A who's cells contain formulas, the cells totals add up and are placed in a single cell which is a negative number
i.e. cells A3:A99 all have formulas with negative totals, that is placed in cell A100 - formula in cell A100 : = sum(A3:A99)

In Spreadsheet 2 the total of cell A100 is placed into cell B1 : =Spreadsheet 1!A100 - would like to have this number to change into a positive

Does ABS that joeu2004 posted not work? i.e.

=ABS(Spreadsheet 1!A100)

 

[snjpverma]

thanks for the code MARK, but would you please tell me why the code gets stuck there on the if statement? i changed the code a bit to include a Else condition . Still it didn't work.

Sub positive()Dim r As Range
Dim c As Range
Set r = Selection
    For Each c In r
        If c.value < 0
        Then c.value = c.value * -1
        Else
        c.Value = c.Value * -2
    End If
    Next
End Sub

 

[MARK858]

The code I posted works for me as long as I have made a selection.
In the code you have posted you have the "Then" on the wrong line.

and why do you need the Else statement as the OP didn't request the other cells to be altered?

 

[Rick Rothstein]

thanks for the code MARK, but would you please tell me why the code gets stuck there on the if statement? i changed the code a bit to include a Else condition . Still it didn't work.

Sub positive()Dim r As Range
Dim c As Range
Set r = Selection
    For Each c In r
        [B][COLOR="#FF0000"]If[/COLOR][/B] c.value < 0
        [B][COLOR="#FF0000"]Then[/COLOR][/B] c.value = c.value * -1
        Else
        c.Value = c.Value * -2
    End If
    Next
End Sub

The Then keyword must be on the same line as the If keyword...

Sub positive()Dim r As Range
Dim c As Range
Set r = Selection
    For Each c In r
        If c.value < 0 Then
            c.value = c.value * -1
        Else
            c.Value = c.Value * -2
        End If
    Next
End Sub

 

[snjpverma]

OMG, what a silly mistake

The re positioning of "Then" did the trick.

Also, I am sorry to go a bit off-topic from OP's question here.

 

[joeu2004]

Spreadsheet 1 [....] cells A3:A99 all have formulas with negative totals, that is placed in cell A100 - formula in cell A100 : = sum(A3:A99)[.] In Spreadsheet 2 the total of cell A100 is placed into cell B1 : =Spreadsheet 1!A100 - would like to have this number to change into a positive

If you know that A100 is always negative, then:

=-'Spreadsheet 1'!A100

If A100 may or may not be negative, then:

=ABS('Spreadsheet 1'!A100)

 

[stumps]

Thanks for all the replies!!

 

[stumps]

=-Spreadsheet 1!A100 works like a bomb