Fill a column's blank spaces with like values partII

I asked this question before, but had a hard time showing which edits I made.

I wrote some code to scan through rows in Column 1... each time it comes across a unique room number, it fills in the blank cells below it with that number. Unfortunately, It's only copying the first cell's value ("1") and pasting it into all rows 3 through 10.

The code I have is: 'Fills in room number column with like room numbers:

AboveCellValue = Range("A3").Offset(-1, 0).Value
For RowIndex = 2 To 10
    For ColIndex = 1 To 1
        If Cells(RowIndex, ColIndex).Value = "" _
        Then Cells(RowIndex, ColIndex).Value = AboveCellValue
    Next ColIndex
Next RowIndex

Here is a pic of what is supposed to happen: http://i802.photobucket.com/albums/yy304/Growler2009/AESProjectPic.jpg

Thank you for your help! -Dan

I'm not sure if I've understood correctly since I find your original code somewhat confused, especially the loop of the columns when you only look at one column, but this might do what you want.

Dim AboveCellValue As Integer
For RowIndex = 2 To 10
    Const ColIndex As Integer = 1
    If Cells(RowIndex, ColIndex).Value = "" Then
        Cells(RowIndex, ColIndex).Value = AboveCellValue
    Else
        AboveCellValue = Cells(RowIndex, ColIndex).Value
    End If
Next RowIndex

Here's another way that doesn't loop. It puts a formula in every blank cell that references the cell above it.

With Range("A2:A10")
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Copy
    .PasteSpecial xlPasteValues
End With

Change the Range to suit your data. Also, it assumes you don't have any other formulas in that range. If you do, they will be converted to values and you probably don't want that.