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

 

[Larry Haydn]

How is your data set up?
Can show us the worksheet or a screenshot?

 

[artz]

Hi Larry,

These two things may help you. There is a view of the work sheet and sample data below for testing. Please let me know if you have further questions.

Thanks,

Art

Date	Data
10/24/58​	540.72​
10/27/58​	539.52​
11/3/58​	543.22​
11/10/58​	554.26​
11/17/58​	564.68​
11/24/58​	554.88​
12/1/58​	557.46​
12/8/58​	556.75​
12/15/58​	562.27​
12/22/58​	573.17​
12/29/58​	572.73​
1/5/59​	587.59​
1/12/59​	592.72​
1/19/59​	595.75​
1/26/59​	596.07​
2/2/59​	593.96​
2/9/59​	581.36​
2/16/59​	587.97​
2/24/59​	602.21​

 

[Alex Blakenburg]

See if this works for you.

Sub wma_11()

    Dim i As Long, j 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
    Dim rowFirst As Long
    
    colData = 2 ' Column containing the data
    colWMA = 6 ' Column to output the WMA
    n = 7 ' Length of the WMA
    rowFirst = 2
    
    For i = rowFirst 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
            If (i - rowFirst + 1) >= n Then
                Cells(i, colWMA).Value = wma / sumWeight
            End If
        End If
    Next i

End Sub

 

[artz]

See if this works for you.

Sub wma_11()

    Dim i As Long, j 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
    Dim rowFirst As Long
  
    colData = 2 ' Column containing the data
    colWMA = 6 ' Column to output the WMA
    n = 7 ' Length of the WMA
    rowFirst = 2
  
    For i = rowFirst 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
            If (i - rowFirst + 1) >= n Then
                Cells(i, colWMA).Value = wma / sumWeight
            End If
        End If
    Next i

End Sub

See if this works for you.

Sub wma_11()

    Dim i As Long, j 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
    Dim rowFirst As Long
   
    colData = 2 ' Column containing the data
    colWMA = 6 ' Column to output the WMA
    n = 7 ' Length of the WMA
    rowFirst = 2
   
    For i = rowFirst 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
            If (i - rowFirst + 1) >= n Then
                Cells(i, colWMA).Value = wma / sumWeight
            End If
        End If
    Next i

End Sub

Hi Alex;
Thanks for your response. The numerical calculation is correct, however the number of blank cell in the output column. The attached screen cap shows that. This was maddening for me to figure out. Either I got what you got, one blank row short or the calculation values were incorrect. If you have some more ideas, please give them a try.
Thanks,
Art

 

[artz]

Alex. sorry about my poor English. I went to Preview view and realized a sentence wasn't complete but couldn't go back to edit it but I think you get the gist of what I was saying. -Art

 

[Alex Blakenburg]

If you just want the same figures to be just 1 row lower just "+ 1" to the output line in my code.

                Cells(i + 1, colWMA).Value = wma / sumWeight

This does mean that your last value will be 1 past the end of the data rows though, is that what you are expecting ?

 

[artz]

If you just want the same figures to be just 1 row lower just "+ 1" to the output line in my code.

                Cells(i + 1, colWMA).Value = wma / sumWeight

This does mean that your last value will be 1 past the end of the data rows though, is that what you are expecting ?

Hi Alex, yah it seems that way but not true. Attached shows the alternative calculation in Column E (actual calculation) compared to the output from the last code you posted in Column F. The alternative calculation takes care of the number of blank cells correctly and also finishes at the correct data row. I prefer the code I posted to the Forum since the code in the alternative calculation is too complicated. If it helps I can post that code which sets up input and output arrays but I prefer the simplest code possible to calculate the WMA. Make sense? Do you want to see that code?

 

[Alex Blakenburg]

OK give this a try:
The key lines are in blue.

Sub wma_11()

    Dim i As Long, j 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
    Dim rowFirst As Long
    
    colData = 2 ' Column containing the data
    colWMA = 6 ' Column to output the WMA
    n = 7 ' Length of the WMA
    rowFirst = 2
    
    For i = rowFirst To Cells(Rows.Count, colData).End(xlUp).Row
        If i < n + rowFirst Then
            Cells(i, colWMA).Value = ""
        Else
            wma = 0
            weight = n
            sumWeight = (n * (n + 1)) / 2
            For j = i - n To i
                wma = wma + Cells(j, colData).Value * weight
                weight = weight - 1
            Next j
            If (i - rowFirst + 1) >= n Then
                Cells(i, colWMA).Value = wma / sumWeight
            End If
        End If
    Next i

End Sub

 

[artz]

OK give this a try:
The key lines are in blue.

Sub wma_11()

    Dim i As Long, j 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
    Dim rowFirst As Long
   
    colData = 2 ' Column containing the data
    colWMA = 6 ' Column to output the WMA
    n = 7 ' Length of the WMA
    rowFirst = 2
   
    For i = rowFirst To Cells(Rows.Count, colData).End(xlUp).Row
        If i < n + rowFirst Then
            Cells(i, colWMA).Value = ""
        Else
            wma = 0
            weight = n
            sumWeight = (n * (n + 1)) / 2
            For j = i - n To i
                wma = wma + Cells(j, colData).Value * weight
                weight = weight - 1
            Next j
            If (i - rowFirst + 1) >= n Then
                Cells(i, colWMA).Value = wma / sumWeight
            End If
        End If
    Next i

End Sub

Hi Alex, congrats! You got it working, thanks! Here are screen caps of the side by side results with the alternative code. I see that you added a new variable named rowFirst. What is the intent of this variable? Thanks, Art