[Solved] Excel VBA to sum right column to left column

by


It’s not the most elegant code, but it works out for you. I totally changed the logic that you used to make ColSumTraining3 sub. Assign the macro to a button, select the desired row and click the button. The value of CumWip will be automattically filled based on the Area.

You can adapt this code to do all rows at once later.

Sub ColSumTraining4()

Dim SawCol As Integer
Dim BakeCol As Integer
Dim CNCCol As Integer
Dim GrindCol As Integer
Dim NC2Col As Integer
Dim NC3Col As Integer
Dim NC4Col As Integer
Dim NC5Col As Integer
Dim CumWipCol As Integer
Dim AreaCol As Integer

SawCol = 2
BakeCol = 3
CNCCol = 4
GrindCol = 5
NC2Col = 6
NC3Col = 7
NC4Col = 8
NC5Col = 9
CumWipCol = 12
AreaCol = 13

Dim AreaVal As String
Dim LastCol As Integer

AreaVal = UCase(Sheets(1).Cells(Selection.Row, AreaCol).Value)

Select Case AreaVal
    Case "NC5"
        LastCol = NC5Col
    Case "NC4"
        LastCol = NC4Col
    Case "NC3"
        LastCol = NC3Col
    Case "NC2"
        LastCol = NC2Col
    Case "GRIND"
        LastCol = GrindCol
    Case "CNC"
        LastCol = CNCCol
    Case "BAKE"
        LastCol = BakeCol
    Case "SAW"
        LastCol = GrindCol
    Case Else
        MsgBox "Please select a valid row"
        Exit Sub
End Select

Dim CumWipSum As Integer
Dim TotalCumWipSum As Integer
TotalCumWipSum = 0

For i = NC5Col To LastCol Step -1
    TotalCumWipSum = TotalCumWipSum + Sheets(1).Cells(Selection.Row, i).Value
Next i

Dim CumWip As Range
Set CumWip = Sheets(1).Cells(Selection.Row, CumWipCol)

CumWip.Value = TotalCumWipSum

End Sub

5

solved Excel VBA to sum right column to left column