VBA to replace negative values with zero's

[Rollnation]

Hello all, I am trying to figure out the VBA to replace all negative values in a specific range, on a specific worksheet "MonteCarlo", Range "S3:AMD163". I have tried unsuccessfully couple of times with the following code:

Amy help from a more seasoned VBA writer would be appreciated!

Sub Replace_Negatives()

'Dim ws As Worksheet
'Dim rng As Range




'Application.ScreenUpdating = False


'Set ws = ThisWorkbook.Sheets("MonteCarlo")
'Set rng = ws.Range("S3:AMD163")


'For Each rng In rng.Cells
'If cell.Value < 0 Then cell.Value = 0

'Application.ScreenUpdating = True
'End Sub

Sub Second_Try()


'Dim RowNum As Long, ColNum As Long
'Application.ScreenUpdating = False
'For ColNum = 19 To Cells(1, Columns.Count).End(xlToLeft).Column
'For RowNum = 3 To Cells(Rows.Count, ColNum).End(xlUp).Row
' If Val(Cells(RowNum, ColNum)) And Cells(RowNum, ColNum) < 0 Then Cells(RowNum, ColNum) = 0
'Next RowNum
'Next ColNum
'Application.ScreenUpdating = True
'End Sub

 

[Rollnation]

I am half way there with this code but i get a runtime error 13 once it see's some cells with #Value . Also I wat this code to run only on sheet "montecarlo"


Sub Replace Zero()
Dim rng As Range
For Each rng In Range("S3:AMD163") ' substitute your range here
If rng.Value < 0 Then
rng.Value = 0
End If
Next
End Sub

 

[Dryver14]

Very similar, in fact I am not sure your first one wont work if you change ThisWorkbook to Activeworkbook

Sub Replace_Negatives()


Dim ws As Worksheet
Dim rng As Range
Dim cell As Range




Application.ScreenUpdating = False




Set ws = ActiveWorkbook.Sheets("MonteCarlo")
Set rng = ws.Range("S3:AMD163")




For Each cell In rng
If cell.Value < 0 Then cell.Value = 0
Next cell
Application.ScreenUpdating = True


End Sub

 

[Rick Rothstein]

I am half way there with this code but i get a runtime error 13 once it see's some cells with [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=VALUE]#VALUE [/URL] . Also I wat this code to run only on sheet "montecarlo"

Sub Replace Zero()
Dim rng As Range
    For Each rng In Range("S3:AMD163") ' substitute your range here
        If rng.Value < 0 Then
           rng.Value = 0
        End If
    Next
End Sub

Does the fact that you have #VALUE ! errors mean your cells are filled with formulas? If so, you want to replace the formulas displaying negative numbers with hard-coded constants (zero values)? If so, should the formulas remain formulas or did you want them converted to constants as well?

 

[Rollnation]

Does the fact that you have #VALUE ! errors mean your cells are filled with formulas? If so, you want to replace the formulas displaying negative numbers with hard-coded constants (zero values)? If so, should the formulas remain formulas or did you want them converted to constants as well?

The array is not formulas but part of another montecarlo VBA that i have written. Once that VBA is finished all of the values in the array will be constants.

So yes I would like to hard code all negatives as constants

 

[Rollnation]

That change worked! However, toward then end of the array I have some #Value cells. Is there a way to modify the code above to only run on cells that are numeric?

 

[Rick Rothstein]

The array is not formulas but part of another montecarlo VBA that i have written. Once that VBA is finished all of the values in the array will be constants.

So yes I would like to hard code all negatives as constants

See if this macro works for you...

[table="width: 500"]
[tr]
	[td]Sub MakeNegativeValuesZero()
  [montecarlo!S3:AMD163] = [IF(montecarlo!S3:AMD163<0,0,IF(montecarlo!S3:AMD163="","",montecarlo!S3:AMD163))]
End Sub[/td]
[/tr]
[/table]

 

[Rollnation]

See if this macro works for you...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub MakeNegativeValuesZero()
  [montecarlo!S3:AMD163] = [IF(montecarlo!S3:AMD163<0,0,IF(montecarlo!S3:AMD163="","",montecarlo!S3:AMD163))]
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

This worked too, thank you. Any benefit of this code over the code above?
This seemed to run pretty quickly.

 

[Rick Rothstein]

This worked too, thank you. Any benefit of this code over the code above?
This seemed to run pretty quickly.

I am guessing my code would be faster given that the other code has to iterate 161,000 cells, examining each one individually, whereas my code lets Excel perform the looping behind the scenes using its optimized cell processing routines to do this.

 

[gornalgremlin]

Without using VBA..

highlight the range or sheet

Ctrl+F find the minus symbol and add a few * wildcards -****
replace with zero.

simple