coba script yang ini:
Sub Tes()
Dim rgHasil As Range, rng As Range
Dim lRow As Long, lHasil As Long
Application.ScreenUpdating = False
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Set rgHasil = Range("w2:w" & lRow)
For Each rng In rgHasil
lHasil = rng.Row
If Range("f" & lHasil).Value < Date Then
rng.Value = DateSerial(Year(Date), Month(Date), Day(Range("f" &
lHasil).Value))
Else
rng.Value = DateSerial(Year(Date), Month(Date) - 1, Day(Range("f" &
lHasil).Value))
End If
Next rng
Application.ScreenUpdating = True
End Sub
salam,
-Miss Jan Raisin-
Pada 24 Oktober 2013 14.08, <[email protected]> menulis:
> **
>
>
> Attachment saya taruh disini ya Miss Jan Raisin...
>
> Soalnya ga bs attach lwt email. (maklum lwt net kantor Miss..)
>
>
>
> http://milis-belajar-excel.1048464.n5.nabble.com/belajar-excel-Mengetahui-Destination-Range-terakhir-pada-Rows-workbook-td5721746.html#a5721769
>
>
>
> ---In [email protected], <[email protected]>
> wrote:
>
> bisakah dikirim sample workbooknya?
> mungkin akan memperjelas kasus yang ditanyakan
>
> salam,
>
>
> -Miss Jan Raisin-
>
>
> Pada 24 Oktober 2013 12.24, <ones_marle_oke@...> menulis:
>
> **
>
>
> Pak Hendrik,
>
>
> Terima kasih untuk codenya.
>
> Namun saat saya modifikasi code tersebut, ternyata ada Bug untuk AutoFill
> nya. Bugnya seperti ini : "AutoFill method of Range class failed"
>
>
> itu kenapa ya pak?
>
> Terima kasih kembali atas bantuan dan penjelesannya.
>
>
> Berikut codenya :
>
> *Success Code :*
>
> Dim iRow As Long
>
> iRow = Cells(Rows.Count, "A").End(xlUp).Row
>
> Range("W2").Select
>
> ActiveCell.FormulaR1C1 = _
>
>
> "=IF(DAY(RC[-17])<DAY(NOW()),DATE(YEAR(NOW()),MONTH(NOW()),DAY(RC[-17])),DATE(YEAR(NOW()),MONTH(NOW())-1,DAY(RC[-17])))"
>
> Selection.AutoFill Destination:=Range("W2:W" & iRow)
>
>
> *After Modified Codes :*
>
> Dim Irow As Long
>
> Irow = shDB_CP.Cells(Rows.Count, "A").End(xlUp).Row
>
> shDB_CP.Range("W2").FormulaR1C1 = _
>
>
> "=IF(DAY(NOW())<DAY(RC[-17]),DATE(YEAR(NOW()),MONTH(NOW()),DAY(RC[-17])),DATE(YEAR(NOW()),MONTH(NOW()-1),DAY(NOW())))"
>
> Selection.AutoFill Destination:=shDB_CP.Range("W2:W" & Irow)
>
> MsgBox "Done", vbOKOnly, "Information"
>
>
> Penjelasan :
>
> shDB_CP => Ini sheets yang akan dituju, sudah dideklarasikan saat open
> workbook
>
>
> ---In [email protected], <[email protected]>
> wrote:
>
> Bukan Pak, hanya baris terakhir saja, karena code lrow = ...End(xlUp).Row
> mengambil baris terakhir yang ada isinya.
>
> Salam,
> HK
>
>
>
>
