Excel vb find blank cells replace with calc

cassid - Dec 5, 2008 at 11:24 AM
 dirtydan - May 10, 2010 at 06:28 AM

I'm trying to write a simple vb script in excel which will find blank cell values in a specific row, and then calculate a new value for each blank cell defined as the average of the immediate value above the empty cell, and the immediate value below the empty cell.


1 reply

Figured it out myself.....used this code:

Sub FillEmpty()
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim cell As Range
For Each cell In Intersect(Selection, _
If Trim(cell) = "" And cell.Row > 1 Then
cell.NumberFormat = cell.Offset(-1, 0).NumberFormat
cell.Value = ((cell.Offset(-1, 0).Value + cell.Offset(+1, 0).Value)/2)
End If
Next cell
Application.Calculation = xlAutomatic 'xlCalculationAutomatic
Application.ScreenUpdating = False
End Sub
Budget Data Cleansing

Thanks for this have use the same code
to average gaps in weekly data
by averaging the columns rather than the rows

but if there is more than one blank cell adjacent to one another this doesn't work? any suggestions on how it can be improved to overcome this?
Do you know how you would change this so that instead it would check the next column value and replace the blank with either 100 or 98.44 based on the value of that column being equal to or greater than 0?