VBA

[crkennedy]

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

 

[EXCEL MAX]

Hello Crkennedy,
first...
When you insert value into input box, code will treat value as text.
You need to find way to convert it into decimal number.
I don't know what you are trying to do but first you can try to do something like this.

    Dim alpha As Variant
    
    alpha = Application.InputBox("What is your alpha value?")
    If InStr(1, alpha, ".") Then
        alpha = CDbl(alpha) / 10 ^ (Len(alpha) - 2)
    Else
        MsgBox "This not right format! The right format is 0.00"
        Exit Sub
    End If

Anything else?