Snasui Excel Forum

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rw As Integer
    Dim lRw As Long
    Dim pcsPck As Integer
    Dim pcsPrz As Single, pckPrz As Single, sOld As Single
    Dim marG1 As Single, marG2 As Single, marG3 As Single
    rw = Target.Row
    marG1 = ActiveSheet.Range("l2").Value
    marG2 = ActiveSheet.Range("m2").Value
    marG3 = ActiveSheet.Range("n2").Value
    
    '//if superuser input pack price in column J
    If Target.Column = 10 Then
        pckPrz = Target.Value
        '//check pieces per pack
        If ActiveSheet.Cells(rw, "f").Value <= 0 Then
            MsgBox "â»Ã´ãÊè¨Ó¹Ç¹ªÔé¹µèÍá¾é¤¡è͹", vbCritical
            Exit Sub
        End If
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            .Calculation = xlCalculationManual
        End With
        With ActiveSheet
                .Unprotect
                sOld = .Cells(rw, "i").Value
                .Cells(rw, "i").Value = Format(Round(pckPrz * (1 + marG2) / _
                                                    (1 + marG3) + 0.00001, 0), "#,##0")
                .Cells(rw, "h").Value = Format(Round(pckPrz * (1 + marG1) / _
                                                    (1 + marG3) + 0.00001, 0), "#,##0")
                .Cells(rw, "g").Value = Format(Round(pckPrz / (1 + marG3) + 0.00001, 2), "#,##0")
                .Cells(rw, "e").Value = Format(Round(.Cells(rw, "g").Value / .Cells(rw, "f").Value + 0.00001, 2), "#,##0.00")
                .Protect
                .EnableSelection = xlUnlockedCells
        End With
        With Sheets("memo")
                .Unprotect
                lRw = .Range("a" & Rows.Count).End(xlUp).Offset(1, 0).Row
                .Cells(lRw, "a") = Format(ActiveSheet.Cells(rw, "b"), "0")
                .Cells(lRw, "b") = Format(Now(), "dd/mm/yy hh:mm")
                .Cells(lRw, "c") = Format(Round(sOld * (1 + marG3) / (1 + marG2) + 0.00001, 0), "#,##0")
                .Cells(lRw, "d") = Format(Target.Value, "#,##0")
                .Protect , AllowFiltering:=True
        End With
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With
    End If
End Sub