View Full Version : VBA code below

02-05-2011, 12:25 PM
Hi Hag've found is and hope you will help me:banghead:
My problem is that I behooves the VBA code below:
In an excel sheet I have in column C with the following dates in an irregular sequence can see the example below:
"C2" date "2011-01-03"
"C3: C8" is in. Celer
"C9" date "2011-01-05"
"C10: C15" is in. Celer
"C16" date "2011-01-06"
"C17: C18" is in. Celer
"C19" date "2011-01-10"
"C20: C23" is in. Celer
"C24" date "2011-01-05"
"C25: C30" is in. Celer
"C26" date "END"

First found dates in column "C" should be copied to any compromising cell to the next date found:
"C2" date "2011-01-03" will be copied to the "C3: C8"
Later found date "C9" date "2011-01-05" and it will be copied to the underlying compromising cell "C10: C15"
And so on until one is found in column "C" text "END" This will stop functioning.

02-05-2011, 12:53 PM
if you want to fill in the blank cells in range C2:C30 with the above cell's value until the cell value is "END" then try:

Option Explicit

Sub fill_blank_cells()

Dim Rng As Range
Dim cll As Range

On Error Resume Next

Set Rng = Range("C2:C30").Cells.SpecialCells(xlCellTypeBlanks)

Rng.FormulaR1C1 = "=R[-1]C"
Loop Until cll.Value = "END"

For Each cll In Rng
cll.Value = cll.Value
Next cll

End Sub

02-05-2011, 02:01 PM
It works as I have in mind.
Thank you very much mancubus