Macro to define columns to be added to a sub-total and create the sub-total column

[chappy]

I want to sum the contents of 4 columns (column U, V, W, X) into a sub-total in column Y. I have attempted to use the following code but it does not work. The columns will always contain numeric data or be completely empty so I have probably tried to include some elements that are not necessary.

Any help would be fantastic.

Private Sub calculateTotalTradeScore()

Dim sourceSheet As Worksheet 'Source Sheet'
Dim rw As Range
Dim col As Range

'Dim quantity As Integer 'variable to store current quantity'
'quantity = convertToInt(Cells(row.row, 5).Value)

Dim PrepScore As Integer 'Preparation score'
Dim EntryScore As Integer 'Entry score'
Dim STOPScore As Integer 'STOP score'
Dim TradeExeScore As Integer 'Trade Execution score'

For Each rw In sourceSheet.UsedRange.Rows 'Looping on each row of Sheet1'

If rw.row <> 1 Then 'Skip first header row'

Dim TotalTradeScore As Double 'Calculated Total Trade score

PrepScore = convertToInt(Cells(rw.row, 22).Value)
EntryScore = convertToInt(Cells(rw.row, 23).Value)
STOPScore = convertToInt(Cells(rw.row, 24).Value)
TradeExeScore = convertToInt(Cells(rw.row, 25).Value)
TotalTradeScore = PrepScore + EntryScore + STOPScore + TradeExeScore

Source.Cells(rw.row, 26).Value = TotalTradeScore 'applies Total trade score'

End If 'Check if not First Row'

Next rw
Exit Sub

End Sub

 

[chappy]

I have partially solved my own question (partially). However, my code does not populate all of the rows in the range that I need.

I am trying to define the range of rows as all those rows from row 2 to the last used row in column "I".
Then I am trying to sum the values in columns "V" to "Y" (i.e. V + W + X + Y) in column Z but only if there is a value greater than or equal to 1 in column "R".

The code is correctly calculating for the first 4 rows but then does not populate for the remaining rows. The code I am using is below and a picture of the sheet is also included to provide some clarity. If anyyone is able to help it would be fantastic. Thanks in advance....

Private Sub calculateTradeScore()
Dim actvSheet As Worksheet 'Source Sheet "DAS_DATA"'
Dim rowrange As Range
Dim TradeID As Range
'Set active sheet in a variable'
Set actvSheet = ActiveWorkbook.Sheets("DAS_DATA")
'Define range of rows from row 2 to last used row in column "Type" column "I"''
Set rowrange = actvSheet.Range("I2:I" & Range("I2").End(xlDown).row)
'Set Range for TradeID'
Set TradeID = actvSheet.Range("R2:R" & Range("R2").End(xlDown).row)
'Set condition If the TradeID is greater than or equal to 1, populate the Total Trade Score column column "Z" with
For Each rowrange In TradeID
    If Cells(TradeID.row, 18) >= 1 Then
    Cells(rowrange.row, 26).Value = Cells(rowrange.row, 22).Value + Cells(rowrange.row, 23).Value + Cells(rowrange.row, 24).Value + Cells(rowrange.row, 25).Value
    Else: Exit For

    End If
    Next rowrange
End Sub

 

[Akuini]

Try this:

Private Sub calculateTradeScore()
Dim n As Long
Dim x As Range
With ActiveWorkbook.Sheets("DAS_DATA")
n = .Range("I2").End(xlDown).Row
    For Each x In .Range("R2:R" & n)
        If x >= 1 Then
            .Cells(x.Row, 26).Value = WorksheetFunction.Sum(.Cells(x.Row, 22).Resize(1, 4))
        End If
    Next
End With
End Sub

If the code doesn't work then can you post your example data above as table not an image, so we can easily copy it to a worksheet?
Instead of uploading an image, there are 2 proper ways to post a table/range, i.e:
1. Copy-paste the range directly to the reply box (but without the column letter & the row number).
2. Using the xl2bb add-in (this is the best way). You can download it by clicking the XL2BB icon in the reply window.

 

[chappy]

Thank you so much for both your help and explanation of the protocol. Your code worked and put me in my happy place. Thank you. I’ll post properly in future. Thanks again!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.