Laufzeitfehler VBA Hilfe
15.12.2020 11:39:45
Rainer-Franz
nachfolgendes läuft bis zum Fehler einwandfrei. Am Ende meiner Nerven. Ihr seid meine letzte Hoffnung, ich kann nicht die Ursache im Laufzeitfehler beim Output finden, 4. letzte Codezeile von unten.
Über Vorschläge wäre ich sehr dankbar.
Liebe Grüße
Rainer
Sub Runthis()
Dim high As Range, low As Range, close1 As Range
Dim output As Range, n As Long, band_factor As Double
Set high = Range("C2:C1001")
Set low = Range("D2:D1001")
Set close1 = Range("E2:E1001")
Set output = Range("H2:H1001")
n = Cells(1, 7) '5 'number of historical periods to look at needed for certain indicators
band_factor = 1.5
CCI_1 high, low, close1, output, n, band_factor
End Sub
Sub CCI_1(high As Range, low As Range, close1 As Range, output As Range, n As Long, band_factor _
As Double)
output(0, 1).Value = "Typical Price"
high0 = high(1, 1).Address(False, False)
low0 = low(1, 1).Address(False, False)
close0 = close1(1, 1).Address(False, False)
output(1, 1).Value = "=(" & high0 & "+" & low0 & "+" & close0 & ")/3"
output(1, 1).Copy output
output(0, 2).Value = "Simple Moving Average"
SMARange = Range(output(1, 1), output(n, 1)).Address(False, False)
output(n, 2).Value = "=average(" & SMARange & ")"
output(n, 2).Copy output.Offset(0, 1)
output(0, 3).Value = "Deviation"
TP = output(n, 1).Address(False, False)
SMARange = output(n, 2).Address(False, False)
output(n, 3) = "=abs(" & TP & "-" & SMARange & ")"
output(n, 3).Copy output.Offset(0, 2)
output(0, 4).Value = "Mean Deviation"
DevRng = Range(output(n, 3), output(n * 2 - 1, 3)).Address(False, False)
output(n * 2 - 1, 4).Value = "=average(" & DevRng & ")"
output(0, 5).Value = "CCI"
TP = output(n * 2 - 1, 1).Address(False, False)
SMA = output(n * 2 - 1, 2).Address(False, False)
MeanDev = output(n * 2 - 1, 4).Address(False, False)
'CCI = output(n * 2 - 1, 5).Address(False, False)
' Hier erhalte ich den Laufzeitfehler - Hilfe.
output(n * 2 - 1, 5).Value = "=(" & TP & "-" & SMA & ")/(" & band_factor & "*" & MeanDev & ")"
Range(output(n * 2 - 1, 4), output(n * 2 - 1, 5)).Copy output.Offset(0, 3)
Range(output(1, 2), output(n - 1, 5)).Clear
Range(output(n, 4), output(n * 2 - 2, 5)).Clear
End Sub