Correct VBA code to output on correct row

[artz]

Hi,
I cobbled together some code that I want to use to create a Weighted Moving Average or WMA. The code is producing the correct numerical values however, the code is not producing the output on the correct row.

For instance when n, the period equals n=7, the output should at n+1. There can't be an output until the first n input data points are processed. As I said earlier, the calculation is correct, matching a similar calculation exactly, but offset by one row. The code below, for an n=7 should have 7 blanks starting at row 2 (for n=7) before the calculated data are output.

Sub wma_11()

Dim i As Long
Dim n As Long
Dim wma As Double
Dim weight As Double
Dim sumWeight As Double
Dim colData As Long
Dim colWMA As Long

colData = 2 ' Column containing the data
colWMA = 6 ' Column to output the WMA
n = 7 ' Length of the WMA

For i = 2 To Cells(Rows.Count, colData).End(xlUp).Row
If i < n + 1 Then
Cells(i, colWMA).Value = ""
Else
wma = 0
weight = n
sumWeight = (n * (n + 1)) / 2
For j = i - n + 1 To i
wma = wma + Cells(j, colData).Value * weight
weight = weight - 1
Next j
Cells(i, colWMA).Value = wma / sumWeight
End If
Next i

End Sub

Notice that the sub name is WMA_11, that's how many code iterations I've been through without getting this issue resolved.

Is there a kind soul in the Forum who can find my error in this code correct when the data output begins?

Any help is greatly appreciated.

Thanks,

Art

 

[Alex Blakenburg]

Firstly thanks for letting me know it is working now.

What is the intent of this variable? Thanks, Art

Your question prompted me to have another look and your original IF statement which is handling the calculation, will also handle the start of the output. So you can actually dispense with the 2nd If statement that I added.

            Next j
            'If (i - rowFirst + 1) >= n Then
                Cells(i, colWMA).Value = wma / sumWeight
            'End If
        End If
    Next i

I added the rowFirst variable because with my additional If statement that we can now remove there were 3 lines that were dependant on which row was the first row with data on it.
In your code you had "+ 1" in multiple places but it was unclear whether this was part of the calculation or a row offset + 1. I have tried to formalise that by having a variable so you can tell where the "+" operation is a an adjustment for what row the data starts on (I probably should have used a name that mentioned it was the first '"data" row and as opposed to the heading row).
So if you insert 3 rows pushing your first row down from 2 to 5, AND change rowFirst to "= 5", you should find it still works (without changing rowFirst it will be out of alignment).

Since the number doesn't change throughout the code it could be defined and set as a constant.
Numbers that appear in code whose meaning is unclear are sometimes referred as Magic Numbers.

 

[artz]

Firstly thanks for letting me know it is working now.

Your question prompted me to have another look and your original IF statement which is handling the calculation, will also handle the start of the output. So you can actually dispense with the 2nd If statement that I added.

            Next j
            'If (i - rowFirst + 1) >= n Then
                Cells(i, colWMA).Value = wma / sumWeight
            'End If
        End If
    Next i

I added the rowFirst variable because with my additional If statement that we can now remove there were 3 lines that were dependant on which row was the first row with data on it.
In your code you had "+ 1" in multiple places but it was unclear whether this was part of the calculation or a row offset + 1. I have tried to formalise that by having a variable so you can tell where the "+" operation is a an adjustment for what row the data starts on (I probably should have used a name that mentioned it was the first '"data" row and as opposed to the heading row).
So if you insert 3 rows pushing your first row down from 2 to 5, AND change rowFirst to "= 5", you should find it still works (without changing rowFirst it will be out of alignment).
Since the number doesn't change throughout the code it could be defined and set as a constant.
Numbers that appear in code whose meaning is unclear are sometimes referred as Magic Numbers.

Hi Alex, thanks for the comments and explanation. Yes, the code could be slightly simplified, but as they say: If it isn't broken, don't fix it. I think I'll leave the code as is. Thanks again for your help. -Art

 

[Alex Blakenburg]

No problem. Glad I could help.