crkennedy
12-01-2020, 06:47 AM
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?
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
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?
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