VBA for Value sign change

Willmonbo

Board Regular
Joined
May 16, 2010
Messages
141
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..
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

AD_Taylor

Well-known Member
Joined
May 19, 2011
Messages
687
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.

Code:
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

Board Regular
Joined
May 16, 2010
Messages
141
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.
 
Last edited:

AD_Taylor

Well-known Member
Joined
May 19, 2011
Messages
687
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

Board Regular
Joined
May 16, 2010
Messages
141

ADVERTISEMENT

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

Board Regular
Joined
May 16, 2010
Messages
141
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

Well-known Member
Joined
May 19, 2011
Messages
687

ADVERTISEMENT

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

Board Regular
Joined
May 16, 2010
Messages
141
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

Well-known Member
Joined
May 19, 2011
Messages
687
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,694
Messages
5,597,571
Members
414,157
Latest member
MrBJBones

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top