I'm trying to create a function for implementing simple exponential smoothing in this VBA spreadsheet.
I've gotten code I've amended from online to suit my needs but it's running incorrectly, it only pastes 0's in column C.
Could anyone advise?
I've gotten code I've amended from online to suit my needs but it's running incorrectly, it only pastes 0's in column C.
Could anyone advise?
VBA Code:
Sub ExpSmoothing()
Dim y As Double
Dim Lastyhat As Double
Dim Currentyhat As Double
Dim i As Integer
Dim alpha As Variant
alpha = Application.InputBox("What is your alpha value?")
If alpha > 0 And alpha < 1 And IsNumeric(alpha) Then
With Worksheets("Data")
.Range("C1").Value = "Exp Smoothing"
For i = 1 To 15
.Range(.Cells(i + 1, 3), .Cells(14, 3)).Value = y
.Range("C2" & i).Value = Lastyhat
Currentyhat = Lastyhat + (alpha * (y - Lastyhat))
Lastyhat = Currentyhat
.Range("C" & i + 1).Value = Currentyhat
Next i
End With
Else
MsgBox ("Please choose an alpha that is numeric and between 0 and 1")
End If
End Sub