ช่วยแก้ไขโค้ด Macro Excel ทีคับ

กระทู้คำถาม

ใครพอจะทราบไหมคคับว่า ผิดแบบนี้จะแก้ยังไงดี เพิ่งหัดโพสนะครับ ขาดตกบกพร่องรายละเอียดส่วนใด บอกมาเลยคับ
Sub Step1()
'
' Step1 Macro
'

'
    Dim i As Integer
    Dim j As Integer

    Sheets("ActCost").Select
    Rows("4:10000").Select
    Selection.Copy
    Sheets("AC0").Select
    Rows("1:1").Select
    ActiveSheet.Paste
    Range("M8").Select
    Sheets("POList").Select
    Range("E18").Select
    ActiveSheet.PivotTables("PivotTable5").PivotCache.Refresh
    Columns("A:F").Select
    Selection.Copy
    Sheets("POList1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E1:E3").Select
    Range("E3").Activate
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    Columns("E:F").Select
    Selection.Style = "Comma"
    Range("F8").Select
    Sheets("CEList").Select
    Range("C15").Select
    ActiveSheet.PivotTables("PivotTable6").PivotCache.Refresh
    Columns("A:H").Select
    Selection.Copy
    Sheets("CEList1").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1:B3").Select
    Range("B3").Activate
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    Columns("F:H").Select
    Selection.Style = "Comma"
    Range("C6").Select
    Cells.Select
    Selection.Replace What:="

blank)", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("F67").Select

    Sheets("POList1").Select
    i = 2
    Do Until Cells(i, 3).Value = ""
    If Cells(i, 1).Value = "" Then
    Cells(i, 1).Value = Cells(i - 1, 1).Value
    End If
    i = i + 1
    Loop
    Columns("A:A").Select
    Selection.Copy
    Columns("G:G").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("E5").Select
    Sheets("CEList1").Select
    i = 2
    Do Until Cells(i, 4).Value = ""
    If Cells(i, 1).Value = "" Then
    Cells(i, 1).Value = Cells(i - 1, 1).Value
    End If
    i = i + 1
    Loop
    i = 2
    Do Until Cells(i, 4).Value = ""
    If Cells(i, 2).Value = "" Then
    Cells(i, 2).Value = Cells(i - 1, 2).Value
    End If
    i = i + 1
    Loop
    i = 2
    Do Until Cells(i, 4).Value = ""
    If Cells(i, 3).Value = "" Then
    Cells(i, 3).Value = Cells(i - 1, 3).Value
    End If
    i = i + 1
    Loop
    Sheets("PO").Select
    Rows("4:10000").Select
    Selection.Copy
    Sheets("PO1").Select
    Rows("1:1").Select
    ActiveSheet.Paste
    Range("M8").Select
    Columns("A:B").Select
    Range("B1").Activate
    Selection.EntireColumn.Hidden = True
    Range("M2:M8").Select
    Selection.EntireRow.Delete
    Cells.Select
    Range("C1").Activate
    Cells.EntireColumn.AutoFit
    Columns("E:E").Select
    Selection.EntireColumn.Hidden = True
    Columns("O:V").Select
    Range("V1").Activate
    Selection.EntireColumn.Hidden = True
    Columns("X:Y").Select
    Range("Y1").Activate
    Selection.EntireColumn.Hidden = True
    Range("AB11").Select
    Range("W2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC4="""","""",VLOOKUP(RC4,POList1!R2C2:R1000C7,6,FALSE))"
    Range("W2").Select
    Selection.Copy
    Range("W3").Select
    Range("W3:W10000").Select
    ActiveSheet.Paste
    Columns("W:W").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("z1").Select
    ActiveCell.FormulaR1C1 = "CurCost"
    Range("z2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-17]="""","""",RC[-17]*RC[-15])"
    Range("z2").Select
    Selection.Copy
    Range("z3:z10000").Select
    ActiveSheet.Paste
    Columns("z:z").Select
    Selection.Style = "Comma"
    Range("c1").Select
     Sheets("PO1List").Select
    Range("D12").Select
    ActiveSheet.PivotTables("PivotTable7").PivotCache.Refresh
    Cells.Select
    Selection.Copy
    Sheets("PO1List1").Select
    Range("A1").Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C1:C3").Select
    Range("C3").Activate
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    Range("H5").Select

    i = 2
    Do Until Cells(i, 5).Value = ""
    If Cells(i, 1).Value = "" Then
    Cells(i, 1).Value = Cells(i - 1, 1).Value
    End If
    i = i + 1
    Loop
    i = 2
    Do Until Cells(i, 5).Value = ""
    If Cells(i, 2).Value = "" Then
    Cells(i, 2).Value = Cells(i - 1, 2).Value
    End If
    i = i + 1
    Loop
    i = 2
    Do Until Cells(i, 5).Value = ""
    If Cells(i, 3).Value = "" Then
    Cells(i, 3).Value = Cells(i - 1, 3).Value
    End If
    i = i + 1
    Loop
    i = 2
    Do Until Cells(i, 5).Value = ""
    If Cells(i, 4).Value = "" Then
    Cells(i, 4).Value = Cells(i - 1, 4).Value
    End If
    i = i + 1
    Loop

    Sheets("JSR").Select
    Cells.Select
    Range("A4").Activate
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("JSR1").Select
    Cells.Select
    ActiveSheet.Paste
    Range("E1:E14").Select
    Range("E14").Activate
    Application.CutCopyMode = False
    Selection.EntireRow.Delete
    Range("C2:C3").Select
    Selection.EntireRow.Delete
    Range("C3:C4").Select
    Selection.EntireRow.Delete
    Columns("A:A").Select
    Selection.Delete Shift:=xlToLeft
    Cells.Select
    Cells.EntireColumn.AutoFit
    Range("C:C,E:E,G:G,I:I").Select
    Range("I1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("K:L").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Copy
    Columns("K:K").Select
    ActiveSheet.Paste
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Columns("F:G").Select
    Selection.Delete Shift:=xlToLeft
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Pre-cal Sales"
    Range("C1").Select
    Selection.Copy
    Range("D1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Pre-cal Project"
    Selection.EntireRow.Delete
    Range("I4").Select

    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight
    Range("E2").Select
    Columns("E:E").ColumnWidth = 0.62
    Columns("F:K").Select
    Selection.Insert Shift:=xlToRight
    Selection.ColumnWidth = 14.25
    Range("b1").Select
    ActiveCell.FormulaR1C1 = "WBS"
    Range("c1").Select
    ActiveCell.FormulaR1C1 = "Pre-cal Sales"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "PO No"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "PO Date"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "Suppilier"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Description"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "Cur"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "Cur Amount"
    Columns("O

").Select
Selection.Copy
Columns("D

").Select
ActiveSheet.Paste
Columns("O

").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Pre-cal Project"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Actual cost"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "PO Committed"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Act+PO"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "Dif"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "Percent"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "Reason"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "Preventive"
    Columns("N:N").Select
    Selection.Delete Shift:=xlToLeft
    j = 2
    Do Until Cells(j, 1).Value = "CONTRIBUTION"
    If Cells(j, 1).Value <> "CONTRIBUTION" Then
    Cells(j, 5).Value = "*"
    Cells(j, 14).Select
    ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
    Cells(j, 15).Select
    ActiveCell.FormulaR1C1 = "=RC[-11]-RC[-1]"
    Cells(j, 16).Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
    Cells(j, 16).Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.00%"
    j = j + 1
    End If

แก้ไขข้อความเมื่อ

ถูกใจให้พอยต์

สมาชิกหมายเลข 1358156

คำตอบที่ได้รับเลือกจากเจ้าของกระทู้

ความคิดเห็นที่ 1

Loop
    Cells(j, 1).Select
    Selection.Font.Bold = True
    Cells(j + 1, 1).Select
    Selection.Font.Bold = True
    Cells(j, 3).Select
    Selection.Font.Bold = True
    Cells(j + 1, 3).Select
    Selection.Font.Bold = True
    Cells(j, 4).Select
    Selection.Font.Bold = True
    Cells(j + 1, 4).Select
    Selection.Font.Bold = True
    Cells(j, 12).Select
    Selection.Font.Bold = True
    Cells(j + 1, 12).Select
    Selection.Font.Bold = True

    Columns("C

").Select
    Range("O1").Activate
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    Range("P3").Select
    Range("A1").Select

    Range("E1").Select
    Range("E1:E141").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Range("A1").Select
    Selection.ClearContents
    Range("A8").Select

        Range("A1:R1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16711680
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    Range("A2").Select
    Range("A2:R2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 3407769
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    Selection.Font.Bold = True
    Range("A3").Select
    Range("A3:R3").Select
    Selection.Font.Bold = True
    With Selection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 16776960
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Rows("3:3").Select
    Selection.Copy
    Rows("4:4").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    Range("A8").Select
    i = 4
    Do Until Cells(i, 1).Value = "CONTRIBUTION"
    If Cells(i, 1).Value <> "CONTRIBUTION" Then
    Rows("3:3").Select
    Selection.Copy
    Cells(i, 1).Select
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    i = i + 1
    End If
    Loop

    i = i + 2
    j = 2
    ตั้งแต่ตรงนี้ไปคือส่วนที่ผิดคับ ไม่รุ้ว่าผิดตรงไหน
Do Until Sheets("PO1List1").Cells(j, 6).Value = ""
If Left(Sheets("PO1List1").Cells(j, 2).Value, 2) = "31" Then
Sheets("jsr1").Cells(i, 2).Value = Sheets("PO1List1").Cells(j, 1).Value
Sheets("jsr1").Cells(i, 6).Value = Sheets("PO1List1").Cells(j, 2).Value
Sheets("jsr1").Cells(i, 7).Value = Sheets("PO1List1").Cells(j, 3).Value
Sheets("jsr1").Cells(i, 8).Value = Sheets("PO1List1").Cells(j, 4).Value
Sheets("jsr1").Cells(i, 9).Value = Sheets("PO1List1").Cells(j, 5).Value
Sheets("jsr1").Cells(i, 10).Value = Sheets("PO1List1").Cells(j, 6).Value
Sheets("jsr1").Cells(i, 11).Value = Sheets("PO1List1").Cells(j, 7).Value
Sheets("jsr1").Cells(i, 12).Value = Sheets("PO1List1").Cells(j, 8).Value
Sheets("jsr1").Cells(i, 1).Value = ""
Sheets("jsr1").Cells(i, 3).Value = ""
Sheets("jsr1").Cells(i, 4).Value = ""
i = i + 1
j = j + 1
Else
i = i + 1
j = j + 1
End If
Loop

j = 2
Do Until Sheets("CEList1").Cells(j, 2).Value = ""
If Sheets("CEList1").Cells(j, 2).Value <> "" Then
Sheets("jsr1").Cells(i, 2).Value = Sheets("CEList1").Cells(j, 1).Value
Sheets("jsr1").Cells(i, 9).Value = Sheets("CEList1").Cells(j, 2).Value
Sheets("jsr1").Cells(i, 7).Value = Sheets("CEList1").Cells(j, 3).Value
Sheets("jsr1").Cells(i, 10).Value = Sheets("CEList1").Cells(j, 4).Value
Sheets("jsr1").Cells(i, 9).Value = Sheets("CEList1").Cells(j, 5).Value
Sheets("jsr1").Cells(i, 10).Value = Sheets("CEList1").Cells(j, 6).Value
Sheets("jsr1").Cells(i, 11).Value = Sheets("CEList1").Cells(j, 7).Value
Sheets("jsr1").Cells(i, 12).Value = Sheets("CEList1").Cells(j, 8).Value
Sheets("jsr1").Cells(i, 1).Value = ""
Sheets("jsr1").Cells(i, 3).Value = ""
Sheets("jsr1").Cells(i, 4).Value = ""
i = i + 1
j = j + 1
End If
Loop

    Columns("A:R").Select
    ActiveWorkbook.Worksheets("JSR1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("JSR1").Sort.SortFields.Add Key:=Range("B2:B203"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("JSR1").Sort
        .SetRange Range("A1:R203")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("L26").Select
    Columns("E:E").Select
    With Selection.Interior
        .PatternColorIndex = xlAutomatic
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Cells.Select
    With Selection.Font
        .Name = "Arial"
        .Size = 9
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    Columns("A:R").Select
    Columns("A:R").EntireColumn.AutoFit
    Range("A1").Select
        Columns("F:F").Select
    Selection.NumberFormat = "@"
    Rows("1:1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    Range("A1:R186").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("e3").Value = ""

End Sub

ถูกใจให้พอยต์

สมาชิกหมายเลข 1358156

▼ กำลังโหลดข้อมูล... ▼

แสดงความคิดเห็น

คุณสามารถแสดงความคิดเห็นกับกระทู้นี้ได้ด้วยการเข้าสู่ระบบ

เข้าสู่ระบบ

อ่านกระทู้อื่นที่พูดคุยเกี่ยวกับ คอมมือใหม่ การพัฒนาซอฟต์แวร์

{room_name}

ช่วยแก้ไขโค้ด Macro Excel ทีคับ

กระทู้ที่คุณอาจสนใจ