Search : in
By :

Excel vb find blank cells replace with calc

Last answer on Aug 2, 2009 11:53:16 pm BST cassid, on Dec 5, 2008 4:24:02 pm GMT 
 Report this message to moderators

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!!

Configuration: Windows XP
Firefox 3.0.4

Best answers for « Excel vb find blank cells replace with calc » in :
Ask your question Reply

1

cassid, on Dec 5, 2008 5:58:13 pm GMT
  • +3

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

   
Reply to cassid

2

 Burly, on Aug 2, 2009 11:53:16 pm BST

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

   
Reply to Burly
Ask your question Reply
They need your help