Excel vb find blank cells replace with calc [Solved/Closed]

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

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.

Thanks!!
See more 

4 replies

Best answer
10
Thank you
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, _
ActiveSheet.UsedRange)
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

Thank you, cassid 10

Something to say? Add comment

CCM has helped 1665 users this month

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


Burly
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?