Changing the value of a cell based on it's contents

[SandsB]

My code looks at the value of a certain cell. I use this command in a for loop to delete the entire row if the value of the cell is blank:
If Cells(x, 2) = "" Then Cells(x, 2).EntireRow.Delete

I want to do some simple math on the original value of the cell. How do I say "Take the original value, subtract 700, if the value is a positive number, make this the new value. If the value is a negative number, the new value should be zero" ????

 

[Boller]

I suggest that you consider avoiding the loop altogether.

To delete rows in your range where column B is blank :-

Range("YourRange").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

Then to change the values of the remaining cells, write code for these steps :
- in a helper column put the formula =IF(B1>700,B1-700,0)
- paste the formula results to column B
- clear the helper column

 

[Joe Was]

If ActiveSheet.Cells(x, 2).Value = "" Then ActiveSheet.Cells(x, 2).EntireRow.Delete
If ActiveSheet.Cells(x, 2).Value - 700 > 0 Then ActiveSheet.Cells(x, 2).Value = ActiveSheet.Cells(x, 2).Value - 700
If ActiveSheet.Cells(x, 2).Value - 700 < 0 Then ActiveSheet.Cells(x, 2).Value = 0

Or:

With ActiveSheet.Cells(x, 2)
If .Value = "" Then .EntireRow.Delete
If .Value - 700 > 0 Then .Value = .Value - 700
If .Value - 700 < 0 Then .Value = 0
End With

 

[Scott Huish]

Joe Was:

That seems to have a problem if a row gets deleted. I think because it then tries to check in the next If what just got deleted.

SandsB:

Perhaps:

With ActiveSheet.Cells(x, 2)
    If .Value = "" Then
        Rows(x).Delete
    Else
        .Value = Application.WorksheetFunction.Max(0, .Value - 700)
    End If
End With

 

[Joe Was]

Ya, like yours better, thought about using ElseIf and doing the row delete at the end.