copy down info

[muzzy]

I have a vb that I got it work in other workbook and sheet. But I am trying to use it on a new worksheet and it will not work. Can some help me with it or give me a formulas that will do the same thing.

Sub copy()
Dim varData As Variant
Dim i As Long

varData = Sheet1.Range("C2:C4000") '// Read in the data.

For i = LBound(varData, 1) + 2 To UBound(varData, 1)
    If IsEmpty(varData(i, 1)) Then
        '// Cell is empty. Copy value from above.
        varData(i, 1) = varData(i - 1, 1)
    End If
Next i

'// Write result to sheet.
Sheet1.Range("B2").Resize(UBound(varData, 1) - LBound(varData, 1) + 1, 1) _
    = varData
End Sub

 

[muzzy]

I have a formulas in column (C) to give me emp ID number from the emp name and ID in column (D) like (Adkins, Smith - 74826) The formulas gives me 74826 so I can match up with my roster. The formulas is this

=IFERROR(IF(D5="","",LOOKUP(10^5,MID(D5,MIN(FIND({1,2,3,4,5,6,7,8,9},D5&123456789)),{1,2,3,4,5})+0)),"")

That formulas goes all the way down column (C) and then I run the vb it gives me
Run Time error 1004
No cells were found

When I hit Debug it highlights (Range("B5:B" & lastRow).SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C")

But when I del the formulas from column (C) your VB work. What else can I do?? By the way thank you with your help on this and sorry if I am making it hard on you.

 

[Joe4]

It looks like in copying over the "blanks" that are being returned by your formula, Excel isn't recognizing those as blanks. This variation seems to work (I was hoping to avoid loops, but not big deal).

Sub MyCopy()


    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column D with data
    lastRow = Cells(Rows.Count, "D").End(xlUp).Row
    
'   Copy data in column C to column B (assuming starting on row 5)
    For myRow = 5 To lastRow
        If Len(Cells(myRow, "C")) > 0 Then
            Cells(myRow, "B") = Cells(myRow, "C").Value
        Else
            Cells(myRow, "B") = Cells(myRow - 1, "B").Value
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[muzzy]

Thanks that did it You Rock