Adding up values and grand totals through vba

[helloakshay]

I am generating certain numbers for a table. I am trying to write a vba code for numbers to be added row wise and column wise.

For example i want the sum of values from c2 to n2 in o2
and then move to o3 Where it should add values from c3 to n3.

I know there is a very simple way of adding values on the sheet, but i am just trying to learn how we can do it in vba.

Private Sub Command Button1_Click()
Dim ab1 As Range
Dim AB2 As Range


'Random numbers
For Each ab1 In Range("c2:n27")
ab1.Offset(0, 0).Value = Application.WorksheetFunction.RandBetween(325, 956)
Next ab1

'Adding Values
For Each AB2 In Range("o2:o27")
AB2.Offset(0, 0).Value = Application.WorksheetFunction.Sum(Range("C2: N2")) 
 'From this point how will i move to the next row c3:n3 to get value on O3 and so on
Next AB2

 

[Luke M]

Welcome to the forum!
First, here's one way to do it, using the worksheetfunction.sum method you've already got going.

Sub ExampleCode()
Dim ab1 As Range
Dim AB2 As Range
Application.ScreenUpdating = False

'Random numbers
For Each ab1 In Range("c2:n27")
    ab1.Value = Application.WorksheetFunction.RandBetween(325, 956)
Next ab1

'Adding Values
For Each AB2 In Range("o2:o27")
    AB2.Value = Application.WorksheetFunction.Sum(AB2.Offset(0, -12).Resize(1, 12))
'From this point how will i move to the next row c3:n3 to get value on O3 and so on
Next AB2

Application.ScreenUpdating = True
End Sub

Now, to explain it. Rather than giving a string to the range (e.g., Range("o2:N2")), we can remember that there are other ways of defining where a range is. Since we are in a loop, we need to keep things relative to AB2. So, we tell the code to offset by -12 columns, getting us to column C. But, we only have a single cell at this point. This is where the Resize comes in. We can then specify from our new point how many rows and how many columns to grab. Grabbing 1 row by 12 columns effectively gives us the range C2:N2.
Then, we when go to next AB2 (which is cell O3), our method will give us C3:N3.

Let me know if you have any questions about either of these methods.

 

[helloakshay]

Crisp, clear, concise, Thank you very much Luke

 

[helloakshay]

How do we do this addition for the values in vertical columns. for example i have values in columns c to g until 5 rows in each column. i want the totals in 6th row of each column. how to write a vba code

 

[NARAYANK991]

Hi Akshay ,

Try this :

Sub ExampleCode()
    Dim ab1 As Range, ab2 As Range
    Dim rw As Range, col As Range
    Dim nc As Integer
    Dim nr As Long

    Application.ScreenUpdating = False

    Set ab2 = Range("c2:N27")
    nc = ab2.Columns.Count
    nr = ab2.Rows.Count

'   Random numbers
     For Each ab1 In ab2
        ab1.Value = Application.WorksheetFunction.RandBetween(325, 956)
    Next ab1

'   Adding Values
     For Each rw In ab2.Rows
        rw.Cells(1, 1).Offset(, nc).Value = Application.WorksheetFunction.Sum(rw.Value)
    Next rw

    For Each col In ab2.Columns
        col.Cells(1, 1).Offset(nr).Value = Application.WorksheetFunction.Sum(col.Value)
    Next col

    Application.ScreenUpdating = True
End Sub

Narayan