Praying... and Praying....

[Factor3]

Upload of file keeps failing, so I can't attach anything. Basically, outputs are supposed to be in B23; monthly returns (which are the ONLY provided information from these reports) are from B10:Unknown (because the return periods might differ).

The error message that I am getting (that makes it so I can't even BEGIN to debug is:
Compile Error:
"Block If without End If".... and I swear if I hear that friggin' beep again this box is goin' out the window....

Anywho, here it is

[vba]Sub MoRet2Stats()
Dim outSheet, inSheet
Dim MoRet(1 To 1000), ComRet(1 To 1000), CumRet(1 To 1000)
Dim RowCnt, FindLast, LastRow
Dim firstDataRow, MoDateCol, MoRetCol, ComRetCol
Dim SDAn, SDCo, MeanAn, MeanCo, GeoMn, ProbL0, ProbG0, ProbGInfl
outSheet = "Report Page"
inSheet = "Return Page"
firstDataRow = 10
MoDateCol = 1
MoRetCol = 2
CumRetCol = 4
ComRetCol = 3
'The original sheet with monthly returns is activated
Sheets(inSheet).Activate
'Because the number of periods of returns is variable, this calcuates the overall number of periods
'of returns, and stores that value inside the variable 'FindLast'
For FindLast = 1 To 1000
LastRow(FindLast) = Cells(firstDataRow + FindLast - 1, MoDateCol).Value
If LastRow <> "" Then
FindLast = FindLast + 1
Else: GoTo BeginCompute
'Now with the # of returns counted, the Cumulative Return Column (CumRetCol) and
'the Continuously Compounded Return Column (ComRetCol) can be calculated and utilized for later variables
BeginCompute:
For RowCnt = 1 To FindLast
CumRetCol(RowCnt) = Cells(firstDataRow + RowCnt - 1, MoRetCol) + 1
ComRetCol(RowCnt) = Exp(Cells(firstDataRow + RowCnt - 1, MoRetCol)) - 1
Next RowCnt
'Now the actual statistical variables can be calculated, and there locations are found below this paragraph
SDAn = SQRT * (Application.Var([firstDataRow:MoRetCol], [FindLast:MoRetCol]) * (253 / 365 * 12))
SDCo = SQRT * (Application.Var([firstDataRow:ComRetCol], [FindLast:ComRetCol]) * (253 / 265 * 12))
MeanAn = Application.Average([firstDataRow:MoRetCol], [FindLast:MoRetCol]) * (253 / 365 * 12)
MeanCo = Application.Average([firstDataRow:ComRetCol], [FindLast:ComRetCol]) * (253 / 265 * 12)
GeoMn = Application.SumProduct([firstDataRow:CumRetCol], [FindLast:CumRetCol]) ^ ((253 / 265 * 12) / FindLast)
'Here is where the location of the statistical variables is placed
Cells(2, 2).Value = SDAn
Cells(2, 3).Value = SDCo
Cells(3, 2).Value = MeanAn
Cells(3, 3).Value = MeanCo
Cells(4, 3).Value = GeoMn

End Sub[/vba]

EDIT: Tommy -> Added Line Breaks for readability

[Charlize]

Upload of file keeps failing, so I can't attach anything. Basically, outputs are supposed to be in B23; monthly returns (which are the ONLY provided information from these reports) are from B10:Unknown (because the return periods might differ).

The error message that I am getting (that makes it so I can't even BEGIN to debug is:
Compile Error:
"Block If without End If".... and I swear if I hear that friggin' beep again this box is goin' out the window....

Anywho, here it is

[vba]Sub MoRet2Stats()
Dim outSheet, inSheet
Dim MoRet(1 To 1000), ComRet(1 To 1000), CumRet(1 To 1000)
Dim RowCnt, FindLast, LastRow
Dim firstDataRow, MoDateCol, MoRetCol, ComRetCol
Dim SDAn, SDCo, MeanAn, MeanCo, GeoMn, ProbL0, ProbG0, ProbGInfl
outSheet = "Report Page"
inSheet = "Return Page"
firstDataRow = 10
MoDateCol = 1
MoRetCol = 2
CumRetCol = 4
ComRetCol = 3
'The original sheet with monthly returns is activated
Sheets(inSheet).Activate
'Because the number of periods of returns is variable,
'this calcuates the overall number of periods of returns, and stores
'that value inside the variable 'FindLast'
For FindLast = 1 To 1000
LastRow(FindLast) = Cells(firstDataRow + FindLast - 1, MoDateCol).Value
If LastRow <> "" Then
FindLast = FindLast + 1
??? What do you want to do ? Cycle through all the not empty rows. Better determine the last
row that is used --- lrow = range("A" & rows.count).end(xlup).row --- will give a number
so FindLast = 10 To lrow -- You also need to have a Next Findlast --
and then the code that needs to be done
Else: GoTo BeginCompute
'Now with the # of returns counted, the Cumulative Return Column (CumRetCol)
'and the Continuously Compounded Return Column (ComRetCol) can be calculated
'and utilized for later variables
BeginCompute:
For RowCnt = 1 To FindLast
CumRetCol(RowCnt) = Cells(firstDataRow + RowCnt - 1, MoRetCol) + 1
ComRetCol(RowCnt) = Exp(Cells(firstDataRow + RowCnt - 1, MoRetCol)) - 1
Next RowCnt
'Now the actual statistical variables can be calculated, and there locations are found below this paragraph
SDAn = SQRT * (Application.Var([firstDataRow:MoRetCol], [FindLast:MoRetCol]) * (253 / 365 * 12))
SDCo = SQRT * (Application.Var([firstDataRow:ComRetCol], [FindLast:ComRetCol]) * (253 / 265 * 12))
MeanAn = Application.Average([firstDataRow:MoRetCol], [FindLast:MoRetCol]) * (253 / 365 * 12)
MeanCo = Application.Average([firstDataRow:ComRetCol], [FindLast:ComRetCol]) * (253 / 265 * 12)
GeoMn = Application.SumProduct([firstDataRow:CumRetCol], [FindLast:CumRetCol]) ^ ((253 / 265 * 12) / FindLast)
'Here is where the location of the statistical variables is placed
Cells(2, 2).Value = SDAn
Cells(2, 3).Value = SDCo
Cells(3, 2).Value = MeanAn
Cells(3, 3).Value = MeanCo
Cells(4, 3).Value = GeoMn
End If
End Sub[/vba]

EDIT: Tommy -> Added Line Breaks for readability

[johnske]

The following is also redundant - what you're effectively saying is "Else Goto the next line", which is what's going to happen anyway.

[vba] Else: Goto BeginCompute
'Now with the # of returns counted, .... (etc)
BeginCompute:[/vba]

you only need... [vba]
'(your code)
Else
For RowCnt = 1 To FindLast
'(your code)
[/vba]

[Factor3]

So are you saying that I should make the changes as done below (see Pink Text).

[vba]Sub MoRet2Stats() [/vba]
[vba]

Dim outSheet, inSheet

Dim MoRet(1 To 1000), ComRet(1 To 1000), CumRet(1 To 1000)
Dim RowCnt, LastRow
Dim firstDataRow, MoDateCol, MoRetCol, ComRetCol
Dim SDAn, SDCo, MeanAn, MeanCo, GeoMn, ProbL0, ProbG0, ProbGInfl
outSheet = "Report Page"
inSheet = "Return Page"
firstDataRow = 10
MoDateCol = 1
MoRetCol = 2
CumRetCol = 4
ComRetCol = 3
'The original sheet with monthly returns is activated
Sheets(inSheet).Activate
'Count the number of rows WITH returns in them AND then store
'that number in the Variable "LastRow" the code should look like??
LastRow = range("A" & rows.count).end(xlup).row

'Now with the # of returns counted, the Cumulative Return Column
'(CumRetCol) and the Continuously Compounded Return Column (ComRetCol)
'can be calculated and utilized for later variables

BeginCompute:
For RowCnt = 1 To LastRow
'This next expression (below these words) is supposed to return a value of
'1+the return found in that cell, but I'm not sure if it actually will because
'I'm not accessing the value property of the cell, just its address....
'is that a correct statement??
CumRetCol(RowCnt) = Cells(firstDataRow + RowCnt - 1, MoRetCol) + 1
ComRetCol(RowCnt) = Exp(Cells(firstDataRow + RowCnt - 1, MoRetCol)) - 1
Next RowCnt
'Now the actual statistical variables can be calculated, and their locations are found below this paragraph
SDAn = SQRT * (Application.Var([firstDataRow:MoRetCol], [FindLast:MoRetCol]) * (253 / 365 * 12))
SDCo = SQRT * (Application.Var([firstDataRow:ComRetCol], [FindLast:ComRetCol]) * (253 / 265 * 12))
MeanAn = Application.Average([firstDataRow:MoRetCol], [FindLast:MoRetCol]) * (253 / 365 * 12)
MeanCo = Application.Average([firstDataRow:ComRetCol], [FindLast:ComRetCol]) * (253 / 265 * 12)
GeoMn = Application.SumProduct([firstDataRow:CumRetCol], [FindLast:CumRetCol]) ^ ((253 / 265 * 12) / FindLast)
'Here is where the location of the statistical variables just calculated is placed
Cells(2, 2).Value = SDAn
Cells(2, 3).Value = SDCo
Cells(3, 2).Value = MeanAn
Cells(3, 3).Value = MeanCo
Cells(4, 3).Value = GeoMn

End Sub
[/vba]

I'm new to VBA, but all of this feedback and help is REALLY great for me, I can't thank you enough for taking the time to help me out. I really look forward to your thoughts & perspectives.

EDIT: Tommy -> Added Line Breaks for readability

[Charlize]

when your data is beginning on row 10 use [vba]For RowCnt = 10 To LastRow [/vba] or use [vba]For RowCnt = 1 to LastRow - 10[/vba] because you start at row 10 to process rows with data. No of rows with data is 20. So lastrow = 30. When you start at line 10 there are going to be errors when you don't substract 10 when you say [vba]For x = 1 to lastrow[/vba]It's because your loop needs to be done 20 times and not 30 times.

Charlize