Selection.AutoFill Destination:=Range("J2:J4224") ต้องการให้เป็น J2:LastRow

[Jetnipit]

เบื้องต้นลองนำโค้ดที่เจอในเว็บนี้นำมาประยุกต์แล้วครับ แต่เวลา Run โค้ดขึ้น Error 400 ตลอด
เป็นมือใหม่กับ Excel ด้วยครับ ขอความกรุณาด้วยครับ

Code: Select all

Sub CalculateTimeIN()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+p
'
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("I:I").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("J:J").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("Q:Q").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("R:R").Select
    Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""?_);_(@_)"
    Columns("I:I").Select
    For Each Kob In Selection
    Kob.Formula = Kob.Formula
    Next
    Columns("Q:Q").Select
For Each Kob In Selection
    Kob.Formula = Kob.Formula
    Next
    ActiveCell.FormulaR1C1 = "TIME8"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]>=(--""7:45 AM""),RC[-2]-(""7:30:00 AM""),""-"")"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]>=(--""7:45 AM""),RC[-1]-(""7:30:00 AM""),""-"")"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J4224")
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]>(--""7:45 AM""),RC[-1]-(""7:30:00 AM""),""-"")"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]>=(--""4:55 PM""),RC[-1]<(--""5:25 PM"")),""0.5"",(IF(RC[-1]>=(--""5:25 PM""),""1"",""-"")))"
    Range("R2").Select
    Selection.AutoFill Destination:=Range("R2:R4224")
End Sub

โค้ดตรงนี้ครับ

Code: Select all

    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J4224")
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]>(--""7:45 AM""),RC[-1]-(""7:30:00 AM""),""-"")"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]>=(--""4:55 PM""),RC[-1]<(--""5:25 PM"")),""0.5"",(IF(RC[-1]>=(--""5:25 PM""),""1"",""-"")))"
    Range("R2").Select
    Selection.AutoFill Destination:=Range("R2:R4224")

[logic]

ช่วยแนบไฟล์ตัวอย่างครับ

[Jetnipit]

อันนี้ยังไม่ได้รันโค้ดครับ แต่มีโค้ดติดไปด้วยแล้วครับ

[logic]

ลองดูโค้ดนี้ครับ

Code: Select all

Sub CalculateTimeIN()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+p
'
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("I:I").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("J:J").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("Q:Q").Select
    Selection.NumberFormat = "[h]:mm;@"
    Columns("R:R").Select
    Selection.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* ""-""?_);_(@_)"
    Columns("I:I").Select
    Columns("I:I").Value = Columns("I:I").Value
'    For Each Kob In Selection
'    Kob.Formula = Kob.Formula
'    Next
    Columns("Q:Q").Select
    Columns("Q:Q").Value = Columns("Q:Q").Value
'    For Each Kob In Selection
'    Kob.Formula = Kob.Formula
'    Next
    ActiveCell.FormulaR1C1 = "TIME8"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]>=(--""7:45 AM""),RC[-2]-(""7:30:00 AM""),""-"")"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]>=(--""7:45 AM""),RC[-1]-(""7:30:00 AM""),""-"")"
    Range("J2").Select
'    Selection.AutoFill Destination:=Range("J2:J4249")
    Selection.Resize(Range("A" & Rows.Count).End(xlUp)).FillDown
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-1]>(--""7:45 AM""),RC[-1]-(""7:30:00 AM""),""-"")"
    Range("R2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(AND(RC[-1]>=(--""4:55 PM""),RC[-1]<(--""5:25 PM"")),""0.5"",(IF(RC[-1]>=(--""5:25 PM""),""1"",""-"")))"
    Range("R2").Select
'    Selection.AutoFill Destination:=Range("R2:R4249")
    Selection.Resize(Range("A" & Rows.Count).End(xlUp)).FillDown
End Sub

[Jetnipit]

โค้ดมันมาหยุดที่ Row 968 จาก Row ทั้งหมด 4248 ครับ

[logic]

อ้อ...เขียนหลุดไป

แก้ตรงนี้ Range("A" & Rows.Count).End(xlUp) เป็น Range("A" & Rows.Count).End(xlUp).Row ครับ

[Jetnipit]

ขอบคุณครับผม สุดยอดมากเลยครับ