asked on

Modify Macro Excel 2007 to change negative values to zero.

I have this lovely piece of code. It runs a paste special-->subtract. What I want is to add to that. If the pasted value is less than zero, I want it to be zero.

Sub SubtractWellsNameSets()
' Subtract Wells & Rename choice sets

If Range("c3") = "" Then
MsgBox "All liquors must have a price, even if it is 0.00"
Exit Sub
End If
If Range("a3") = "" Then
MsgBox "You must first paste all your liquors in the worksheet."
Exit Sub
End If
    Range("G2").Copy
    Range("C3").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract, _
        SkipBlanks:=False, Transpose:=False
'Here is where I want to make any values less than zero to be zero.
Range("A3").Select

Range(ActiveCell, ActiveCell.End(xlDown)).Select
    For Each cell In Selection
        cell.Value = cell.Value & ": Sub"
    Next cell
    Range("A3:B3").Select
    Range(Selection, Selection.End(xlDown)).Copy
    Sheets("CreateChoiceSets").Select

    Range("M" & Rows.Count).End(xlUp).Offset(1).Select
    'Range("M2").Select
    ActiveSheet.Paste
    Sheets("SubMaker").Select
    Range("B3:C3").Select
    Range(Selection, Selection.End(xlDown)).Copy
    Sheets("CreateChoiceSets").Select
'This needs to be the first blank cell in column E instead of E87
    Range("E" & Rows.Count).End(xlUp).Offset(1).Select
    'Range("E87").Select
    ActiveSheet.Paste
'Here, I am removing duplicates in column A
    Sheets("SubMaker").Select
    Range("A3").Select
   Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
   Range("A3").Select
    Range(Selection, Selection.End(xlDown)).Copy
    Sheets("CreateChoiceSets").Select
    Range("A" & Rows.Count).End(xlUp).Offset(1).Select
    ActiveSheet.Paste
    Range("A2").Select
    
    Call ResetCondFormatsChoiceSets
End Sub

Open in new window

I can email a workbook to anyone who would like it, but cannot load here.

Rgonzo1971

Hi,

pls try

For Each cell In Selection
    cell.Value = Application.max(0, cell.Value)
Next

Open in new window

Regards

Professor J

you can call the sub below from the above sub

Sub negativetozero()

Dim RowNum as Long, ColNum as Long

Application.ScreenUpdating = False

For ColNum = 1 to Cells(1, Columns.Count).End(xlToLeft).Column
  For RowNum = 2 to Cells(Rows.Count, ColNum).End(xlUp).Row
     If Val(CStr(Cells(RowNum, ColNum))) < 0 Then cells(RowNum, ColNum) = 0
  Next RowNum 
Next ColNum

Application.ScreenUpdating = True

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Professor J

the code, i provided works nice converts all negative values to zero, assuming that your data starts from column A and it will work on each subsequent column to the right untill the last column with data.

change the For ColNum = 1 to other numbers if your first column is not A, column B is 2 and C3 and so on.

Anne Troy

ASKER

So simple, Rory!!!!!