Previous Topic Next Topic

Excel vb find blank cells replace with calc

Solved/Closed
cassid - Dec 5, 2008 at 11:24 AM
 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!!
Related:

1 response

Answer 1 / 1
cassid
Dec 5, 2008 at 12:58 PM
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
10
Burly
Aug 2, 2009 at 06:53 PM
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
0
Matt
Mar 27, 2010 at 01:10 AM
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?
0
dirtydan
May 10, 2010 at 06:28 AM
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?
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses
help me to add particular cells
mathewmunna -
mathew munna -

2 responses
How to calculate position (1st,2nd,3rd) in excel....?
abdulwajid -
TrowaD -

1 response
How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Calculation of positions in excel
Mohsin -
Dausayi -

12 responses