find last populated cell and autofill column- vba

[whitoulias]

[TABLE="width: 192"]
<colgroup><col style="width:48pt" span="3" width="64"> </colgroup><tbody>[TR]
[TD="width: 64"]DATA[/TD]
[TD="width: 64"]DATE
[/TD]
[TD="width: 64"] PRICE
[/TD]
[/TR]
[TR]
[TD="class: xl51"]DATA[/TD]
[TD="class: xl50, width: 64"]20120621[/TD]
[TD="class: xl52, align: right"] 118.1000
[/TD]
[/TR]
[TR]
[TD="class: xl51"]DATA[/TD]
[TD="class: xl50, width: 64"]20120621[/TD]
[TD="class: xl52, align: right"]149.7700[/TD]
[/TR]
[TR]
[TD="class: xl51"]DATA[/TD]
[TD="class: xl50, width: 64"][/TD]
[TD="class: xl52, align: right"]94.8900[/TD]
[/TR]
[TR]
[TD="class: xl51"]DATA[/TD]
[TD="class: xl50, width: 64"][/TD]
[TD="class: xl52, align: right"]137.5900[/TD]
[/TR]
[TR]
[TD="class: xl51"]DATA[/TD]
[TD="class: xl50, width: 64"][/TD]
[TD="class: xl52, align: right"]114.5500[/TD]
[/TR]
[TR]
[TD="class: xl51"]DATA[/TD]
[TD="class: xl50, width: 64"][/TD]
[TD="class: xl52, align: right"]145.2700[/TD]
[/TR]
[TR]
[TD="class: xl51"]DATA[/TD]
[TD="class: xl50, width: 64"][/TD]
[TD="class: xl52, align: right"]114.5500[/TD]
[/TR]
[TR]
[TD="class: xl51"]DATA[/TD]
[TD="class: xl50, width: 64"][/TD]
[TD="class: xl52, align: right"]92.4000
[/TD]
[/TR]
[TR]
[TD="class: xl51"]DATA[/TD]
[TD="class: xl50, width: 64"][/TD]
[TD="class: xl52, align: right"]145.2700[/TD]
[/TR]
</tbody>[/TABLE]

i have 3 columns (data, date & price)
What i would like to do is find the last populated cell in 'date' column and autofill it based on column 'data'.
The tricky thing is that the date form is text and while autofilling manually it changes
Any thoughts?

Thanx

 

[VoG]

Try

Sub atest()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B2:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

 

[Domski]

Or:

With Range("B" & Rows.Count).End(xlUp)
    Range(.Offset(1, 0), Range("B" & Range("A" & Rows.Count).End(xlUp).Row)) = .Value
End With

Dom

 

[Sektor]

Sub FillData()
    Dim lastA As Long, lastB As Long
    
    lastA = Cells(Rows.Count, "A").End(xlUp).Row
    lastB = Cells(Rows.Count, "B").End(xlUp).Row
    
    If lastB < lastA Then
        Range("B" & lastB & ":B" & lastA).FillDown
    End If
    
End Sub

 

[whitoulias]

Try

Sub atest()
Dim LR As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
With Range("B2:B" & LR)
    .SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[-1]C"
    .Value = .Value
End With
End Sub

Perfect
thanx a lot

 

[whitoulias]

Or:

With Range("B" & Rows.Count).End(xlUp)
    Range(.Offset(1, 0), Range("B" & Range("A" & Rows.Count).End(xlUp).Row)) = .Value
End With

Dom

Works as well
thanx

 

[whitoulias]

Sub FillData()
    Dim lastA As Long, lastB As Long
    
    lastA = Cells(Rows.Count, "A").End(xlUp).Row
    lastB = Cells(Rows.Count, "B").End(xlUp).Row
    
    If lastB < lastA Then
        Range("B" & lastB & ":B" & lastA).FillDown
    End If
    
End Sub

It works fine
though it keeps the format in text
Thank you all