CODEWITHSUNDEEP
vbaexcel
Xi Wang
Xi Wang

Reputation: 37

How to add sum value for odd number rows in Excel VBA

I need help with my VBA code, I want to get the total value (display in Sheets("Report").Cells(LastLine,i).Value) of each odd row. In my code, I only can get total odd and even row values. Thanks!
Here is my VBA code:

 'LastLine is a row number which have blank content
    Dim LastLine As Long
    LastLine = Range("B" & Rows.count).End(xlUp).Row + 2

   For i = 4 To 21
    Sheets("Report").Cells(LastLine, y).Select
      With Selection
          .Font.Bold = True
          .Font.Size = 10
          .Interior.Color = RGB(135, 206, 250)
      End With
     Sheets("Report").Cells(LastLine, i).Value = WorksheetFunction.Sum(Range(Cells(2, i), Cells(65536, i)))
    Next

Upvotes: 0

Views: 1324

Answers (1)

Chrismas007
Chrismas007

Reputation: 6105

First NEVER use Select

Let's try this:

'LastLine is a row number which have blank content
    Dim LastLine As Long, RunSum As Long
    LastLine = Range("B" & Rows.count).End(xlUp).Row + 2

 For i = 4 To 21
    With Sheets("Report").Cells(LastLine, y) 'Perhaps (LastLine, i)?
        .Font.Bold = True
        .Font.Size = 10
        .Interior.Color = RGB(135, 206, 250)
    End With
    RunSum = 0
    For CurRow = 3 to LastLine - 1 Step 2
        RunSum = RunSum + Cells(CurRow, i).Value
        Sheets("Report").Cells(LastLine, i).Value = RunSum
    Next CurRow
Next i

Upvotes: 1

Related Questions