Dear Miss Jan Raisin, Woooooowwwww... 0_0 looping dengan for ya... Ga kepikiran... :-(
berjalan dengan lancar..... Terima kasih atas masukan dan pemberian script codenya... ---In [email protected], <[email protected]> wrote: 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, <ones_marle_oke@... mailto:ones_marle_oke@...> 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 http://milis-belajar-excel.1048464.n5.nabble.com/belajar-excel-Mengetahui-Destination-Range-terakhir-pada-Rows-workbook-td5721746.html#a5721769 ---In [email protected] mailto:[email protected], <[email protected] mailto:[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@... mailto: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] mailto:[email protected], <[email protected] mailto:[email protected]> wrote: Bukan Pak, hanya baris terakhir saja, karena code lrow = ...End(xlUp).Row mengambil baris terakhir yang ada isinya. Salam, HK
