Microsoft Excel - enter data in cells from other cells

Asked By Cherifa Hima on 18-Feb-13 09:34 PM
Hi,
I need  a vba code that will allow me to enter data in empty cells. The condition should be as follow: if a cell = 0, pick a number from a cell before it ( the closest cell before it and where the content <>0). But,  if all cells before it equal 0 then pick a number from the cell after it ( or the closest cell after it and where the content <>0).
for example, A1= .15   A6= .2  A7= .2  A9=.2   A10= .2
                   B2= .16    B3= .16  B6=.1  B7=.1  B8=.1  B9 =.1  B10=.1
                   C1 =.11  C2= .11   C3=.11  C4= .11   C5=.11   C8=.2
Thanks.


  1 2 3 4 5 6 7 8 9 10 11 12
A 0.15 0.16 0.12 0.2 0.2  
B 0.16 0.9 0.1  
C 0.11 0.2 0.16
D 0.15 0.18 0.2  
E 0.15 0.16 0.12 0.11 0.9 0.1 0.12 0.16 0.17 0.18 0.19  
F       0.13               0.16
Harry Boughen replied to Cherifa Hima on 19-Feb-13 07:02 PM
Hello Cherifa Hima,
I think the following code will do what you want.  You need to name the range that you want it to work on TABLE.

Option Explicit

Sub CellFill()

Dim rngTable, rngCell As Range
Dim lngCount As Long

Set rngTable = Range("TABLE")

Do
    For Each rngCell In rngTable
      If rngCell.Value = "" Then
        rngCell.Value = rngCell.Offset(0, 1).Value
      End If
      If rngCell.Value = "" Then
        If rngCell.Column > 1 Then
          rngCell.Value = rngCell.Offset(0, -1).Value
        End If
      End If
    Next rngCell
    lngCount = rngTable.Cells.Count - Application.WorksheetFunction.CountA(rngTable)
Loop While lngCount > 0

End Sub

Regards
Harry
Cherifa Hima replied to Harry Boughen on 19-Feb-13 08:45 PM
Thanks Harry for taking time to answer my question. 

I moved the double if up and it worked fine.

Sub CellFill()


Dim rngTable, rngCell As Range
Dim lngCount As Long


Set rngTable = Range("TABLE")


Do
    For Each rngCell In rngTable
    If rngCell.Value = "" Then
        If rngCell.Column > 1 Then
          rngCell.Value = rngCell.Offset(0, -1).Value
        End If
      End If


      If rngCell.Value = "" Then
        rngCell.Value = rngCell.Offset(0, 1).Value
      End If
          Next rngCell
    lngCount = rngTable.Cells.Count - Application.WorksheetFunction.CountA(rngTable)
Loop While lngCount > 0


End Sub