VBA for Value sign change

[Willmonbo]

I'm dealing with a dataset that has columns that should be negative values, often those values are pasted from a source file which has them as positive numbers, thus far I am doing a paste special with the mutiply operation having copied a "-1" to the clipboard.

I'd really like to avoid the few extra steps and have VBA do it all with any range I would select.

I thank you in advance..

 

[AD_Taylor]

Try the code below. Make the selection you want to make negative first and then run the macro as normal.
The code does accomodate multiple areas in the selection as well, i.e. if you selected some cells in Column A and then some in Column D it will still work without a problem.

Sub ConvertToNegValues()    
Dim rng As Range
    Dim area As Range
    Dim c As Range


    Set rng = Selection
    
    For Each area In rng.Areas
        For Each c In area.Cells
            c.Value = c.Value * (-1)
        Next c
    Next area
End Sub

 

[Willmonbo]

Thanks for your code, precisely what I needed. Can I ask you for one touchup, I imagined it to toggle back to positive if the macro is run again on the same selected range, that would be ideal in the event of a negative conversion in error, I'd greatly appreciate if you can send me that update.

I must add that looking at the code, I don't understand why it doesn't toggle back and forth, but I guess I'm missing something.

 

[AD_Taylor]

It's strange that you say it doesn't - on mine it does!

For example, a +10 becomes -10 and then when run again becomes +10 again. Which version of Excel are you using btw?

 

[Willmonbo]

I'm using excel 2007, since my last reply I have notice that once I run the macro I can't even type in a positive value without it turning negative.

 

[Willmonbo]

Just to explain myself a bit better with the above comment, I now have those cells that I ran the macro on, change all values (even when typed as positive) automatically to negative even without running the macro again, and nothing helps even running the macro again which should’ve conceivably changed it back to positive but it doesn’t, I’d greatly appreciate if you can shed some light on this perplexing situation.

 

[AD_Taylor]

Have you placed the code inside a standard module (Insert > Module), exactly as it was written?

It sounds like you may have integrated it with a Worksheet_Change event? So that the code runs every time a cell value is changed, which would make positive values impossible.

As another check what happens when you specifically type in a negative value?

 

[Willmonbo]

Thanks, my bad.


now works like a charm, much appreciated.

 

[Willmonbo]

can I pull out one more from you, i'd like for a warning message to appear if those columns are left with positive values, it should still allow to proceed but when leaving the page and still positive values are left in those designated columns, i'd like a message to pop up.

can you help me with that code as well?

 

[AD_Taylor]

No problem. Something like this?

Basically a message box will be shown stating where positive values still occur and asking if you want to leave the sheet or not. If Yes is clicked the sheet moves on, otherwise the current sheet is left activated. If there are no positive values the message isn't shown at all.

You'll need to specify the columns to check for positive values in the variable. All code is commented so should be self explanatory.

Also this code does need to go into the Sheet module for the sheet that you want it to work on.

Private Sub Worksheet_Deactivate()
    Dim wsMove          As Worksheet
    Dim rngColsCheck    As Range
    Dim rngSglCol       As Range
    Dim strColNames     As String
    
    'MsgBox Variables
    Dim strMsg          As String
    Dim mbOpts          As Long
    Dim strTitle        As String
    
    Set wsMove = ActiveSheet
    Me.Activate
    
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
    'Specify the columns to check for positive values here
    Set rngColsCheck = Me.Range("C:C, D:D, H:H")
    
    For Each rngSglCol In rngColsCheck.Columns
        If WorksheetFunction.CountIf(rngSglCol, ">0") > 0 Then
            'Build a list of columns that still contain positive values
            strColNames = strColNames & Left(Replace(rngSglCol.Address, "$", ""), 1) & ", "
        End If
    Next rngSglCol
    
    'If no columns have a postive value clean up and then End Sub so message isn't shown
    If strColNames = "" Then
        wsMove.Activate
        GoTo clean_up
    End If
    
    'Strip the final separator from the list of columns
    strColNames = Left(strColNames, Len(strColNames) - 2)
    
    'Set message shown
    strMsg = "There are positive values left in the columns: " & strColNames & vbCrLf & _
                "Would you still like to leave the sheet?"
    
    'Set style of MsgBox by commenting out the appropriate option below
    mbOpts = vbYesNo + vbCritical       'Yes/No buttons with a Red X image (Stop Warning)
    'mbOpts = vbYesNo + vbInformation    'Yes/No buttons with a blue i image (Information warning)
    
    'Set title of message box
    strTitle = "Leave Sheet?"
    
    If MsgBox(strMsg, mbOpts, strTitle) = vbYes Then
        wsMove.Activate
    End If
    
clean_up:
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub