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 :
[VBA] Detecting changes in cell Show[VBA] Detecting changes in cell The Event Change feature of a sheet will detects the change in the active cell but it gives no information about the content. The example given below will help you to find out if the cell was changed,...
Excel tips : How to insert date in a cell ShowExcel tips : How to insert date in a cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press CTRL ¯+ ;¯ in the chosen cell. To insert current time, press CTRL¯+...
Avoid duplicates in Excel ShowAvoid duplicates in Excel In order to avoid duplication when encoding in a column from an excel sheet: take the conditional format on the first cell under the headings (eg A2) choose the following formula:...
Spreadsheets - Cell Selection ShowCell Selection Spreadsheets are powerful tools for working with data. However, to work with data, it is necessary to have tools to rapidly choose the required cells. Line Selection An entire line can be chosen by clicking directly on the line...
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
  • +1

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