Anne Troy
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.
I can email a workbook to anyone who would like it, but cannot load here.
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
I can email a workbook to anyone who would like it, but cannot load here.
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
change the For ColNum = 1 to other numbers if your first column is not A, column B is 2 and C3 and so on.
ASKER
So simple, Rory!!!!!
pls try
Open in new window
Regards