Formel mit Zellbereich als Argument in Cells()-Sch

Bild

Betrifft: Formel mit Zellbereich als Argument in Cells()-Sch
von: Markus
Geschrieben am: 22.02.2005 17:49:55
Liebe Forum-Mitglieder,
folgendes Problem:
ich möchte in einem VB-Script einer Zelle eine Formel zuweisen.
Die Formel verlangt als Argument einen Zellbereich.
Da der Bereich in seiner Länge variabel sein kann, möchte ich einen Zähler benutzen.
Wenn ich als Argument den Bereich in der Range-Schreibweise einbinde, übernimmt Excel zwar die Formel, kann aber das Argument nicht interpretieren (Ergebnis: "WERT?").
So (oder so ähnlich) möchte ich es machen (N ist der Zähler, der die Länge des Bereichs festlegt):
'Mittelwert
ActiveSheet.Cells(N + 4, 14).Select
ActiveSheet.Cells(N + 4, 14).Activate
ActiveCell.Formula = "=AVERAGE(Range(Cells(2,14),Cells(N+1,14)))"

In einer anderen Schreibweise (die ich aber aufgrund mangelnder Flexibilität und schlechter Übersicht nicht weiterverwenden möchte) funktioniert es:
'Mittelwert
ActiveSheet.Cells(N + 4, 14).Select
ActiveSheet.Cells(N + 4, 14).Activate
ActiveCell.Formula = "=AVERAGE(R[-3]C:R[-" & N +2 & "]C)"
Wer kann mir einen Tipp geben, wie ich mit "Cells()" weiterkomme?
Vielen Dank und viele Grüße
Markus

Bild

Betrifft: AW: Formel mit Zellbereich als Argument in Cells()-Sch
von: Bert
Geschrieben am: 22.02.2005 18:03:00
"Wer kann mir einen Tipp geben, wie ich mit "Cells()" weiterkomme?"
Geht nicht, da der Konstrukt ein String ist.
Bert
Bild

Betrifft: AW: Formel mit Zellbereich als Argument in Cells()
von: bst
Geschrieben am: 22.02.2005 19:48:54
Auch Hallo,
ActiveCell.Formula = "=AVERAGE(" & Range(Cells(2, 14), Cells(N + 1, 14)).Address & ")"
cu, Bernd
Bild

Betrifft: AW: Formel mit Zellbereich als Argument in Cells()
von: Bert
Geschrieben am: 22.02.2005 21:42:37
Hmm...hätte ich aber auch rausfinden müssen, toll!
Bert
Bild

Betrifft: AW: Formel mit Zellbereich als Argument in Cells()
von: Markus
Geschrieben am: 23.02.2005 09:13:13
Hallo Bernd und Bert,
vielen Dank für Eure Hilfe und gleich eine weitere Frage:
Gibt es zu
ActiveCell.Formula = "=AVERAGE(" & Range(Cells(2, 14), Cells(n + 1, 14)).Address & ")"
ein Äquivalent zu Array-Formeln (z.B. Häufigkeit)?
Hintergrund: Habe in einer Spalte n Werte stehen ein paar Zeilen drunter (in der gleichen Spalte) will ich erst Anzahl, Mittelwert, Median etc. (alles untereinander) eintragen. Darunter soll eine Häufigkeitstabelle (Histogramm) erstellt werden, derart daß erst die einzelnen Teilintervalle (Bins) untereinander geschrieben werden und darunter die entsprechenden Häufigkeiten. Die Ursprungswerte, Statistischen Werte, Bins und korrespondierenden Häufigkeiten sind dann also alle jeweils untereinander in ein und der selben Spalte.
Bis zu den Häufigkeiten (Frequency) hat auch alles gefunzt.
Mein Konstrukt
ActiveSheet.Range(ActiveSheet.Cells(n + 12 + m, 14),
ActiveSheet.Cells(n + 12 + 2 * m, 14)).Select
Selection.FormulaArray = "=FREQUENCY( _
" & ActiveSheet.Range(ActiveSheet.Cells(2, 14), _
ActiveSheet.Cells(n + 1, 14)).Address & " , _
" & ActiveSheet.Range(ActiveSheet.Cells(n + 10, 14), _
ActiveSheet.Cells(n + 12 + m - 2, 14)).Address & "
)"
funzt so aber nicht. Die Argumente in Frequency (Daten und entsprechende Teilintervalle) habe ich auch als Range deklariert abgespeichert. Am liebsten würde ich diese (dataRange, binRange) als Argumente übergeben (bei Formeln, die nur in eine Zelle eingetragen werden geht das auch).
Offensichtlich muß man Selection.FormulaArray anders ansprechen als ActiveCell.Formula
Any Idea?
Vielen Dank und viele Grüße
Markus
Bild

Betrifft: Es funzt doch! ABER....
von: Markus
Geschrieben am: 23.02.2005 09:36:42
Ich habe eben mal spasseshalber alles in EINE Zeile geschrieben und siehe da : es funzt!
Offensichtlich findet durch den Compiler eine Interpretation als String statt (die Formel steht ja in "".

Übrigens, in voller Schönheit und mit der Möglichkeit Application.WorksheetFunction sowie
deklarierte Ranges zu benutzen:
Dim dataRange As Range
Dim binRange As Range
Dim i As Integer
Dim n As Integer
Dim m As Integer

m = 30

Sheets("bladiblubb").Select
Sheets("bladiblubb").Activate
i = 1
Do
i = i + 1
Loop Until ActiveSheet.Cells(i, 1) = ""

n = i - 2

'Berechnen von Mittelwert und Median
Worksheets("bladiblubb").Activate

Set dataRange = ActiveSheet.Range(ActiveSheet.Cells(2, 14), ActiveSheet.Cells(n + 1, 14))

'Anzahl, Mittelwert und Median
ActiveSheet.Cells(n + 5, 14) = n
ActiveSheet.Cells(n + 6, 14) = Application.WorksheetFunction.Average(dataRange)
ActiveSheet.Cells(n + 7, 14) = Application.WorksheetFunction.Median(dataRange)

'Erzeugen der Bins (Intervallgrenzen) für das Histogramm
ActiveSheet.Cells(n + 10, 14) = 13
ActiveSheet.Cells(n + 11, 14).Select
ActiveSheet.Cells(n + 11, 14).Activate
ActiveCell.FormulaR1C1 = "=R[-1]C+0.1"
ActiveSheet.Cells(n + 11, 14).Select
ActiveSheet.Cells(n + 11, 14).Activate
Selection.Copy
ActiveSheet.Range(ActiveSheet.Cells(n + 12, 14), ActiveSheet.Cells(n + 12 + m - 2, 14)).Select
ActiveSheet.Paste

Set binRange = ActiveSheet.Range(ActiveSheet.Cells(n + 10, 14), ActiveSheet.Cells(n + 12 + m - 2, 14))

'Erzeugen des Histogramms
ActiveSheet.Range(ActiveSheet.Cells(n + 12 + m, 14), ActiveSheet.Cells(n + 12 + 2 * m, 14)).Select
' alte Version (deaktiviert):
'Selection.FormulaArray = "=FREQUENCY(" & ActiveSheet.Range(ActiveSheet.Cells(2,
14), ActiveSheet.Cells(n + 1, 14)).Address & " , " & ActiveSheet.Range(ActiveSheet.Cells(n + 10, 14), ActiveSheet.Cells(n + 12 + m - 2, 14)).Address & ")"
'neue Version (mit Application.WorksheetFunction):
Selection.FormulaArray = Application.WorksheetFunction.Frequency(dataRange, binRange)


Hab ich alle hier im Forum gelernt (DANKE an alle!)
Viele Grüße
Markus
 Bild

Beiträge aus den Excel-Beispielen zum Thema "Formel mit Zellbereich als Argument in Cells()-Sch"