Script not Running

[akin]

Pls, I am new to VBA. I copy this script below from a book on how to calculate standard deviation on a data in a sheet and record in separate column.
The problem I am having is that the script is not running. Please, what could be the reason? Or rather help me modify it. Thanks.
The code:
Dim WindowlLength As Integer
Dim StdDevRange As Range

WindowLength = 20

'Column headings
Cells(1, "A") = "Symbol"
Cells(1, "B") = "Date"
Cells(1, "E") = "Close"
Cells(1, "F") = "Price Change"
Cells(1, "G") = "Log Change"
Cells(1, "H") = "Spike"

'Calculate price change and log of price change
DataRow = 3
While Cells(DataRow, "A") <> ""
If Cells(DataRow, "A") = Cells(DataRow - 1, "A") Then
Cells(DataRow, "F") = Cells(DataRow, "E") / Cells(DataRow - 1, "E")
Cells(DataRow, "G") = Log(Cells(DataRow, "E") / Cells(DataRow - 1, "E"))
End If
DataRow = DataRow + 1
Wend

'Calculate price spikes in standard deviations
DataRow = WindowLength + 3
While Cells(DataRow, "A") <> ""
If Cells(DataRow, "A") = Cells(DataRow - _
WindowLength - 1, "A") Then
Set StdDevRange = Range("G" & (DataRow - _
WindowLength) & ":" & "G" & (DataRow - 1))
Cells(DataRow, "H") = Cells(DataRow, "F") / _
(Application.WorksheetFunction.StDev(StdDevRange) _
* Cells(DataRow - 1, "E"))
End If

[rollis13]

​Just to add missing crosspost link: LINK (https://www.ozgrid.com/forum/index.php?thread/1229832-script-not-running/#post1250585)

[akin]

Thanks. But I don't understand.

[SamT]

He means that you posted this question on ozgrid.com and didn't tell anyone. That is considered rude.

Your code, as shown in your post. won't run because it is not all there. If we can't see all the code, we can't troubleshoot it.

Also... Why are you using DataRow in the second While Loop

[akin]

I am very sorry for that. I did not look at it that way but my desperation. Thanks.
You are right. That is not the whole code; I copied the first aspect. The code is long and I did not what to stress you, thinking that if one worked I will be able to manipulate my way through the rest. The whole code is:
"

Sub spikes ( )
Dim WindowlLength As Integer
Dim StdDevRange As Range

WindowLength = 20

'Column headings
Cells(1, "A") = "Symbol"
Cells(1, "B") = "Date"
Cells(1, "E") = "Close"
Cells(1, "F") = "Price Change"
Cells(1, "G") = "Log Change"
Cells(1, "H") = "Spike"

'Calculate price change and log of price change
DataRow = 3
While Cells(DataRow, "A") <> ""
If Cells(DataRow, "A") = Cells(DataRow - 1, "A") Then
Cells(DataRow, "F") = Cells(DataRow, "E") / Cells(DataRow - 1, "E")
Cells(DataRow, "G") = Log(Cells(DataRow, "E") / Cells(DataRow - 1, "E"))
End If
DataRow = DataRow + 1
Wend

'Calculate price spikes in standard deviations
DataRow = WindowLength + 3
While Cells(DataRow, "A") <> ""
If Cells(DataRow, "A") = Cells(DataRow - _
WindowLength - 1, "A") Then
Set StdDevRange = Range("G" & (DataRow - _
WindowLength) & ":" & "G" & (DataRow - 1))
Cells(DataRow, "H") = Cells(DataRow, "F") / _
(Application.WorksheetFunction.StDev(StdDevRange) _
* Cells(DataRow - 1, "E"))
End If

Dim DataRow As Long
Dim SummaryRow As Long
Dim LastDataRow As Long
Dim ReferenceTicker As String
Dim Count As Integer
Dim Criterion As Double
Dim IterationIndex As Integer
Dim ScoreColumnName As String
Dim SummaryColumn_2 As Integer
Dim RangeString As String

IterationIndex = 0
For Criterion = 0.5 To 4 Step 0.5
DataRow = 2
Count = 0
ReferenceTicker = Cells (DataRow, "A")

‘Create a sum for each ticker and store in column G
While Cells (DataRow, "A") <> ""
If Cells (DataRow, "A") = ReferenceTicker Then
If Abs (Cells (DataRow, "F")) > Criterion Then
Count = Count + 1


End If
Else
ReferenceTicker = Cells (DataRow, "A")
DataRow = DataRow - 1
Cells (DataRow, "G") = Count
Count = 0
End If
DataRow = DataRow + 1
Wend
Cells (DataRow - 1, "G" ) = Count

‘Create summary table-add data after each pass through the file
LastDataRow = DataRow - 1
SummaryRow = DataRow + 2
ScoreColumnName = ">" & Criterion & " StdDev"
SummaryColumn_2 = IterationIndex + 2
Cells (SummaryRow, SummaryColumn_2) = ScoreColumnName
If Iteration Index = 0 Then
Cells (SummaryRow, "A") = “Ticker"
End If
SummaryRow = SummaryRow + 1

For DataRow = 2 To LastDataRow
If Cells (DataRow, "G") <> “ " Then
Cells (SummaryRow, "A") = Cells (DataRow, "A")
Cells (SummaryRow, SummaryColumn_2) = Cells (DataRow, "G")
SummaryRow = SummaryRow + 1
End If
Next DataRow

‘Begin next pass through the file
IterationIndex = IterationIndex + 1
Next Criterion

‘Format summary table
RangeString = "A" & (LastDataRow + 4) &_
":" &"I" & (SummaryRow - 1)
Range (RangeString) .Select
Selection.NumberFormat = "0"
End Sub

And workbook to apply it to is attached as first attachment

After the vba script has been run, it will produce a table similar to the one in second attachment
While the reason for using DataRow in the second loop, according to the writer of the script is shown in the table in the third attachment.
In all, this script set to achieve on the workbook:
1. Align record in the Merged worksheet that consist five different data each consisting 252 rows of data base on date and remove unmatched ticker or row of data
2. Remove blank row of data
3. Create extra columns for calculation like 'price change', 'log of price change', 'standard deviation' and 'price spike'
4. Finally, produce the summary table as seen in the attached table
I really appreciate your kind gesture. Once again, I am sorry for my early attitude. Thanks.

[SamT]

I found several Wrong characters and typos, including some invisible ones, which suggests that this code was copied from some place other tha a VBA Code Module
I made one modification and added a suggested "Wend". This code now compiles. Without seeing the source Data I cannot comment on its Logic.

Note how the indentation and line spacingmakes it easier to see loops and sections.


Option Explicit

Sub spikes()
Dim WindowlLength As Integer
Dim StdDevRange As Range
Dim DataRow As Long
Dim SummaryRow As Long
Dim LastDataRow As Long
Dim ReferenceTicker As String
Dim Count As Integer
Dim Criterion As Double
Dim IterationIndex As Integer
Dim ScoreColumnName As String
Dim SummaryColumn_2 As Integer
Dim RangeString As String

WindowlLength = 20 '<---A magic number. I have no idea of its significance. SamT

'Column headings
Cells(1, "A") = "Symbol"
Cells(1, "B") = "Date"
Cells(1, "E") = "Close"
Cells(1, "F") = "Price Change"
Cells(1, "G") = "Log Change"
Cells(1, "H") = "Spike"

'Calculate price change and log of price change
DataRow = 3
While Cells(DataRow, "A") <> ""
If Cells(DataRow, "A") = Cells(DataRow - 1, "A") Then
Cells(DataRow, "F") = Cells(DataRow, "E") / Cells(DataRow - 1, "E")
Cells(DataRow, "G") = Log(Cells(DataRow, "E") / Cells(DataRow - 1, "E"))
End If
DataRow = DataRow + 1
Wend

'Calculate price spikes in standard deviations
DataRow = WindowlLength + 3

While Cells(DataRow, "A") <> "" '<-----------------No Wend in sight. SamT
'If DataRow is ever less than WindowSize, this will cause a crash
If Cells(DataRow, "A") = Cells(DataRow - WindowlLength - 1, "A") Then '----Math? SamT
Set StdDevRange = Range("G" & (DataRow - WindowlLength) & ":" & "G" & (DataRow - 1))
Cells(DataRow, "H") = Cells(DataRow, "F") / (Application.WorksheetFunction.StDev(StdDevRange) * Cells(DataRow - 1, "E"))
End If

'Suggested Wend
Wend '<--- Not here in original. Added by SamT just to check Compiling------------------------------------------------------------

IterationIndex = 0

For Criterion = 0.5 To 4 Step 0.5
DataRow = 2
Count = 0
ReferenceTicker = Cells(DataRow, "A")

'Create a sum for each ticker and store in column G
While Cells(DataRow, "A") <> ""
If Cells(DataRow, "A") = ReferenceTicker Then
If Abs(Cells(DataRow, "F")) > Criterion Then
Count = Count + 1
End If
Else
ReferenceTicker = Cells(DataRow, "A")
DataRow = DataRow - 1
Cells(DataRow, "G") = Count
Count = 0
End If
DataRow = DataRow + 1
Wend

Cells(DataRow - 1, "G") = Count

'Create summary table-add data after each pass through the file
LastDataRow = DataRow - 1
SummaryRow = DataRow + 2
ScoreColumnName = ">" & Criterion & " StdDev"
SummaryColumn_2 = IterationIndex + 2
Cells(SummaryRow, SummaryColumn_2) = ScoreColumnName

If IterationIndex = 0 Then
Cells(SummaryRow, "A") = "Ticker"
End If
SummaryRow = SummaryRow + 1

For DataRow = 2 To LastDataRow
If Trim(Cells(DataRow, "G")) <> "" Then '<---Modified by SamT----------------------------------------------------------------------------
Cells(SummaryRow, "A") = Cells(DataRow, "A")
Cells(SummaryRow, SummaryColumn_2) = Cells(DataRow, "G")
SummaryRow = SummaryRow + 1
End If
Next DataRow

'Begin next pass through the file
IterationIndex = IterationIndex + 1
Next Criterion

'Format summary table
RangeString = "A" & (LastDataRow + 4) & ":" & "I" & (SummaryRow - 1)
Range(RangeString).Select
Selection.NumberFormat = "0"

End Sub

[akin]

I am highly appreciative. I will try it out. Thanks alot.