Home>

Sub test ()

Dim i As Long
Dim j As Long
Dim h As Long

j = 0
h = 0

For i = 2 To Cells (Rows.Count, 1) .End (xlUp) .Row

Cells (i, 4) = Cells (i, 2) * Cells (i, 3)

j = j + Cells (i, 4)
h = h + 1
Next

MsgBox"Total:"&j&vbLf&_
"Average:"&j/h, vbOKOnly,"Result"

End Sub

The above is Excel VBA,
A B C D
Date Quantity Unit Price Amount
1 January 26 304
January 2 68 305
3 January 84 972
4 January 53 684
January 5 34 703
January 6 56 628
Jan 7 12 619
January 8 92 356
9 January 31 949
Jan 10 19 229
11 January 54 507
Jan 12 35 666
13 January 83 728
January 14 68 272
January 15 78 236
January 16 87 334
17 January 34 490
18 January 32 650
January 19 68 348
January 20 98 744

This is a macro that calculates the total and average in MSGBOX by calculating

.
However, when calculating the average value, the total is divided by the number of pieces, but it cannot be expressed that h = 0 and h = h + 1.
Could you tell me anyone?

I ’m a VBA beginner

vba
  • Answer # 1

    Is it safe to say that the For statement is a repetition?
    If h is added one by one at the same time as adding to j in the iteration, h becomes the number.

    I'd like to set up a breakpoint and step through it to see how h changes. Please check the method of step execution (I think it will come out if you check "VBA breakpoint").