CODEWITHSUNDEEP
excelvba
Aaron
Aaron

Reputation: 1

Looping calculations in VBA

I have created a loop to iterate through each row and I want it to add up the values for each column in each row and sum them at the end. I know how to do it as a formula but I am new to VBA. I want it to sum the values multiply by a weight factor for columns B-F then output the values in column G. I know I need to use a for loop to iterate through every row that is not blank as that amount will be changing as the sheet is updated. The formula version would be:

`=((5*(i,1))+(4*(i,2))+(3*(i,3))+(2*(i,4))+(1*(i,5))`

and I want this value output into cells(i,6)

So far I have:

`set wbk1 = Application.ActiveWorkbook
lr = wbk1.Sheets("sheet3").Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 to Rows.Count
If i < lr Then
End If
Next i`

The formula part in the middle of the loop is where I am stuck at I believe.

Thanks!

Upvotes: 0

Views: 2615

Answers (1)

SJR
SJR

Reputation: 23081

Try this. You can use Range or Cells notation, but in this case the latter seems more intuitive. That said, you would be better off using worksheet formulae for this.

Sub x()

Dim wbk1 As Workbook
Dim lr As Long, i As Long

Set wbk1 = Application.ActiveWorkbook

With wbk1.Sheets("Sheet3")
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    For i = 2 To lr
        .Cells(i, "G") = 5 * .Cells(i, "B") + 4 * .Cells(i, "C") + 3 * .Cells(i, "D") + _
                      2 * .Cells(i, "E") + .Cells(i, "F")
    Next i
End With

End Sub

Upvotes: 1

Related Questions