Option Explicit
Const rowQty As Long = 17
Const rowAvg As Long = 18
Const rowAmt As Long = 19
Const rowPurch As Long = 24
Private Sub Worksheet_Change(ByVal Target As Range)
Dim colTarget As Long, rowTarget As Long
Dim rQty As Range, rAvg As Range, rAmt As Range, rPurch As Range, rCENT_KG As Range
'remember the location of the first cell changed
With Target.Cells(1, 1)
colTarget = .Column
rowTarget = .Row
End With
'set some range variables to make it easier to keep straight
Select Case colTarget
Case 16, 24, 32, 40, 48, 56, 64, 72, 80
Set rQty = Cells(rowQty, colTarget)
Set rAvg = Cells(rowAvg, colTarget)
Set rAmt = Cells(rowAmt, colTarget)
Set rPurch = Cells(rowPurch, colTarget)
Set rCENT_KG = Cells(rowPurch, colTarget + 1)
Case 17, 25, 33, 41, 49, 57, 65, 73, 81
Set rQty = Cells(rowQty, colTarget - 1)
Set rAvg = Cells(rowAvg, colTarget - 1)
Set rAmt = Cells(rowAmt, colTarget - 1)
Set rPurch = Cells(rowPurch, colTarget - 1)
Set rCENT_KG = Cells(rowPurch, colTarget)
Case Else
Exit Sub
End Select
Application.EnableEvents = False
'calc amount
If Len(rQty.Value) = 0 Then
rAmt.ClearContents
ElseIf Len(rAvg.Value) > 0 Then
rAmt.Value = rQty.Value / rAvg.Value
End If
'do caclucation that I don't understand
Select Case colTarget
Case 16, 24, 32, 40, 48, 56, 64, 72, 80
If rAmt.Value > 0 Then
rCENT_KG.Value = rPurch.Value / rAmt.Value * 100
End If
Case 17, 25, 33, 41, 49, 57, 65, 73, 81
rPurch.Value = rCENT_KG * rAmt.Value / 100#
End Select
Application.EnableEvents = True
End Sub