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

#### SandsB

##### Well-known Member
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" ????

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Boller

##### Banned
I suggest that you consider avoiding the loop altogether.

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

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

##### MrExcel MVP
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

##### MrExcel MVP
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:

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

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

Replies
1
Views
44
Replies
0
Views
140
Replies
4
Views
127
Replies
6
Views
92
Replies
1
Views
71

1,191,533
Messages
5,987,137
Members
440,082
Latest member
belodelokelo

### 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.

### Which adblocker are you using?

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

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