我没有使用任何break语句,但是我的代码没有超出一行执行。我在同一代码中以不同的方式使用同一循环5次,每个循环运行1000次以上。
这可能是原因吗?
ActiveCell.FormulaR1C1 = "1 Month"
'The above line executes but beyond this things are working
i = x
j = x
Dim t3 As Date, t4 As Date, temp As Integer
Dim r As Single
temp = 0
Do Until temp = 1
t1 = "=DAY(dt(j))"
t2 = "=DAY(dt(i))"
t3 = "=MONTH(dt(j))"
t4 = "=MONTH(dt(i))"
If (t2 < t1) Then ' condition 1
If (t3 <> t4) Then ' condition 2
upf(0) = "=COUNTIF(chp(i-1):chp(j),"">0"")" 'counting +ve numbers
dwf(0) = "=COUNTIF(chp(i-1):chp(j),""<0"")" 'counting -ve numbers
upb(0) = "=COUNTIF(chb(i-1):chb(j),"">0"")"
dwb(0) = "=COUNTIF(chb(i-1):chb(j),""<0"")"
std(0) = "=STDEV(price(i-1):price(j))" 'standard deviation
sdb(0) = "=STDEV(bch(i-1):bch(j))"
mx = "=MAX(price(i-1):price(j))"
mn = "=MIN(price(i-1):price(j))"
ActiveCell.FormulaR1C1 = mn / mx - 1 'calculating maximum drawdown
ActiveCell.Offset(0, 1).Select
mx = "=MAX(bch(i-1):bch(j))"
mn = "=MIN(bch(i-1):bch(j))"
ActiveCell.FormulaR1C1 = mn / mx - 1
temp = 1
End If
End If
Loop 'end loop
最佳答案
好了,既然您已经提供了很多数据,我想我知道您实际上在尝试做什么。如果我对您的理解正确,那么您将数据按行排列,例如,日期在“A”列中,日期的值在“B”列中,例如“C”列中的差异。
您希望遍历每一行,并在“C”列中找到“正差数”,然后计算该月的标准差。
以下代码应适用于您的解决方案:
Sub Analysis_Prices()
Dim X as Integer, Cur_Row As Integer, No_Mths As Integer, No_Pos As Integer
Dim Data() As Variant
ReDim Data(1 to 3,1 to 1) As Variant
Dim Positives() As Double
ReDim Positives(1 to 1) As Double
Cur_Row = 1
For X = 2 to 10000
'This for loop goes through all values in column A.
If Range("A" & X).Value = "" Then Exit For
If Month(Range("A" & Cur_Row).Value) <> Month(Range("A" & X).Value) Then
No_Mths = No_Mths + 1
ReDim Preserve Data(1 to 3,1 to No_Mths) As Variant
Data(1,No_Mths) = DateSerial(Year(Range("A" & Cur_Row).Value),Month(Range("A" & Cur_Row).Value),1)
Data(2,No_Mths) = No_Pos
No_Pos = 0
Data(3,No_Mths) = STDDEV(Positives)
Cur_Row = X
ReDim Positives(1 to 1) As Double
End If
If Range("C" & X).Value > 0 Then
No_Pos = No_Pos + 1
ReDim Preserve Positives(1 to No_Pos) As Double
Positives(No_Pos) = Range("C" & X).Value
End If
Next X
No_Mths = No_Mths + 1
ReDim Preserve Data(1 to 3,1 to No_Mths) As Variant
Data(1,No_Mths) = DateSerial(Year(Range("A" & Cur_Row).Value),Month(Range("A" & Cur_Row).Value),1)
Data(2,No_Mths) = No_Pos
Data(3,No_Mths) = STDDEV(Positives)
End Sub
并将此代码用于StdDev函数(来自https://www.mrexcel.com/forum/excel-questions/206375-standard-deviation-array.html):
Function Mean(Arr) As Double
Dim Sum As Single
Dim i As Integer
Dim k1 As Long, k2 As Long
Dim n As Long
k1 = LBound(Arr)
k2 = UBound(Arr)
Sum = 0
n = 0
For i = k1 To k2
n = n + 1
Sum = Sum + Arr(i)
Next i
Mean = Sum / n
End Function
Function StdDev(Arr) As Double
Dim i As Integer
Dim avg As Single, SumSq As Single
Dim k1 As Long, k2 As Long
Dim n As Long
k1 = LBound(Arr)
k2 = UBound(Arr)
n = 0
avg = Mean(Arr)
For i = k1 To k2
n = n + 1
SumSq = SumSq + (Arr(i) - avg) ^ 2
Next i
StdDev = Sqr(SumSq / (n - 1))
End Function