Weighted Moving Average Calculation in VBA

[artz]

Hi,
I am trying to perform a Weighted Moving Average Calculation in VBA. The code below seems simple enough but generates an error. Input data are in column B starting at row 2. WMA output is in column G starting at row 2.

Is there someone in the Forum who can determine what is causing the error? Here's the code:

Sub wma()

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 = 7 ' Column to output the WMA
n = 5 ' Length of the WMA

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

Any help would be greatly appreciated.

Thanks,

Art

 

[Joe4]

Look at your first iteration in your loop.

n = 5
i = 2

So this line:

For j = i - n + 1 To i

will translate to:

For j = 2 - 5 + 1 To 2

which simplifies to:

For j = -2 To 2

so the first value for j is -2.

However, you are trying to use i as a row number here:

wma = wma + Cells(j, colData) * weight

It is impossible to have a negative row number!
Row -2 makes no sense, which is why you are getting error.

That is the best way of debugging loops like this. Work through the details and figure out what the values are at the time.
If you use F8, you can step through your code line-by-line, one line at a time, and if you hover over any variable, it will show you the exact value of that variable at that point in time.

 

[artz]

Look at your first iteration in your loop.

n = 5
i = 2

So this line:

For j = i - n + 1 To i

will translate to:

For j = 2 - 5 + 1 To 2

which simplifies to:

For j = -2 To 2

so the first value for j is -2.

However, you are trying to use i as a row number here:

wma = wma + Cells(j, colData) * weight

It is impossible to have a negative row number!
Row -2 makes no sense, which is why you are getting error.

That is the best way of debugging loops like this. Work through the details and figure out what the values are at the time.
If you use F8, you can step through your code line-by-line, one line at a time, and if you hover over any variable, it will show you the exact value of that variable at that point in time.

 

[artz]

Hi Joe4,
Thanks for your response and comments. I did step through the code using F8 and the variable values look incorrect. I did not originate this code so I am not sure what I need to do to correct the issues.
Do you have some modifications that would correct the problem with the loops? I understand that there can't be a negative row number.
Thanks,
Art

 

[Joe4]

Hi Joe4,
Thanks for your response and comments. I did step through the code using F8 and the variable values look incorrect. I did not originate this code so I am not sure what I need to do to correct the issues.
Do you have some modifications that would correct the problem with the loops? I understand that there can't be a negative row number.
Thanks,
Art

Without seeing your data, and having a full understanding of how you want it to interact with that particular data structure, I cannot say for sure.

Note that if you can create the formula you need in Excel (using Excel workbook formulas), you can usually use most of those formulas in VBA by using "Application.WorksheetFunction..." or "Evaluate".

 

[artz]

Here's a screen cap of the worksheet.