Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.06.2024 19:56:24
17.06.2024 19:39:46
Anzeige
Archiv - Navigation
1360to1364
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Parameter in Formel einbauen

Parameter in Formel einbauen
14.05.2014 11:02:53
Georg
Hallo Excel Welt,
gleich vorab: ich weis nicht, wo ich das Thema recherchieren soll. Ist sicher schon x-mal gefragt, aber ich krieg es nicht raus.
Also eigentlich ganz einfach. Ich schreibe per Makro eine Summenformel in eine Zelle und will jetzt den Bereich der Formel mittels eines vorher definierten Parameters bestimmten.
Das hab ich mal so versucht: (... und "select" ist auch noch drin => SCHÄM!)
statt:
Sub Makro11()
Dim ZEILE As Integer
ZEILE = Range("CE5").End(xlDown).Row
Range("J1").Select
    ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[4]C:R[14999]C)"
End Sub
hab ich:
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R[4]C:R["&ZEILE&"]C)"
gemacht. Da mault VBA aber.
Wie geht das? Und das Select kann ich doch sicher auch vermeiden, oder?
Danke und Gruß Georg

13
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Parameter in Formel einbauen
14.05.2014 11:09:20
EtoPHG
Hallo Georg,
Achte darau, was (Fehlermeldung) und wo (gelb markierte Codezeile) VBA 'mault'.
Stelle diese Informationen in der Anfrage zur Verfügung, sonst müssen die Helfer alles nachbauen!
In deinem Fall: Zeilen und Spalten-Index-Variablen sollen/müssen immer mit Long definiert werden, egal ob da am Schluss 1 oder 1048576 in der Variablen steht!
...und das Select vermeiden, ist hier doch simple:
Range("J1").FormulaR1C1 = "=SUBTOTAL(9,R[4]C:R[" & Zeile & "]C)"
Gruess Hansueli

das Leben kann so schön sein.... ;-) Danke!
14.05.2014 11:21:39
Georg
Hallo Hansueli,
klasse danke!
Der Vollständigkeit halber: VBA machte die Zeile rot und schrieb: "Fehler beim Kompilieren, Erwartet: Anweisungsende"
Ist noch ne Frage drin? Was macht den Unterschied zwischen Long und Integer? Ich dachte mit Integer kann ich immer alles erschlagen....
Aber jetzt klappt es perfekt und ich hab wieder was gelernt.
Gruß Georg

Anzeige
AW: das Leben kann so schön sein.... ;-) Danke!
14.05.2014 11:27:13
EtoPHG
Hallo Georg,
Schau in der VBE Hilfe nach, wie gross der Zahlenumfang der beiden Typen ist.
Da wirst du schnell feststellen, das ein Integer die Anzahl Zeilen in einem 2007XL Blatt nicht abbilden kann, was zu einem Integer-Overflow führt!
Gruess Hansueli

AW: das Leben kann so schön sein.... ;-) Danke!
14.05.2014 11:27:40
Daniel
Integer ist definiert bis 2^15 = 32768
Excel 2003 hat aber 2^16 = 65536 Zeilen, Excel 2007ff 2^20 = c.a. 1,04 Mio Zeilen.
dh wenn du mal dein Tabellenblatt ausnutzt, überschreitest du diese Grenzen.
Desweitern ist es so, dass die aktuellen Computersysteme auf die Verarbeitung von parallel 32-Bit ausgelegt sind und damit den Daten Datentyp LONG optimal unterstützen.
Der Typ Integer hat keine Vorteile gegenüber Long, sondern nur den Nachteil des kleinernen Zahlenraums.
Gruß Daniel

Anzeige
Danke Hansueli und Daniel!
14.05.2014 11:37:22
Georg
Prima, ich stelle vorsichtshalber all meine Integer auf Long um.
Danke und noch nen schönen Tag
Georg

AW: Parameter in Formel einbauen
14.05.2014 11:12:06
Hajo_Zi
Hallo Georg,
die sinnlosen Zeilen habe ich auskommentiert.
Option Explicit
Sub Makro11()
'    Dim ZEILE As Integer
'    ZEILE = IIf(IsEmpty(Cells(Rows.Count, 83)), Cells(Rows.Count, 83).End(xlUp).Row, Rows. _
Count)
Range("J1").FormulaR1C1 = "=SUBTOTAL(9,R[4]C:R[14999]C)"
End Sub

83 ?
14.05.2014 11:26:40
Georg
Hallo Hajo,
da ist ja was ganz neues (für mich!!) drin! Was macht die 83?
Und ist es richtig. dass da "ZEILE = IIf(IsEmpty..." mit 2x "I" steht?
Danke auch Dir für deine Hilfe - schnell und cool wie immer :-)
Gruß Georg

Anzeige
AW: 83 ?
14.05.2014 11:30:37
Hajo_Zi
83 ist Spalte CE, Dein Befehl lief nicht und der richtige Befehl würde ein falsches Ergebnis liefern falls letzte Zelle in der Spalte belegt.
Gruß Hajo

Danke Hajo owt
14.05.2014 11:34:51
Georg
.

Ja IIF gibt's...
14.05.2014 11:35:10
EtoPHG
Georg,
Cursor im VBE auf IIF setzten und F1 gibt dir Auskunft.
Die Antwort von Hajo ist aber eh völliger Quatsch und vermutlich aus dem Stress entstanden, möglichst schnell irgendetwas zu posten!
Gruess Hansueli

ok, danke! owt
14.05.2014 11:41:15
Georg
.

AW: Parameter in Formel einbauen
14.05.2014 11:21:38
Daniel
Hi
das Verkettungssymbol "&" muss immer mit einem Leerzeichen vom Restlichen Code abgetrennt werden.
das liegt daran, dass VBA das "&"-Zeichen noch für andere Zwecke verwendet (bspw um im Code eine Hex-Zahl verwenden zu können) und über die Leerzeichen diese verschiedenen Verwendungszwecke unterschieden werden können.
ist zwar kein Fehler, aber da du die absolute Zeilennummer der letzen genutzen Zeile ermittelst, _ solltest du auch die Zeilennummer auch absolut in die Formel einbringen, sonst verschiebt sich das ganze nochmal nach unten.

Sub Makro11()
Dim ZEILE As Long
ZEILE = Range("CE5").End(xlDown).Row
Range("J1").FormulaR1C1 = "=SUBTOTAL(9,R[4]C:R" & Zeile & "C)"
End Sub
da ich solche Zerissenen Formeln im Code nicht mag, bringe ich die Paramter per Replace in die Formel ein. Das ist besonder dann vorteilhaft, wenn ein Parameter mehrfach in der Formel vorkommt:
Sub Makro11()
Dim ZEILE As Long
Dim Formel as String
ZEILE = Range("CE5").End(xlDown).Row
Formel =  "=SUBTOTAL(9,R[4]C:RxxxC)"
Formel = Replace(Formel, "xxx", Zeile)
Range("J1").FormulaR1C1 = Formel
End Sub
Gruss Daniel

Anzeige
coool!
14.05.2014 11:32:25
Georg
Hallo Daniel,
"replace" ... das ist ja lässig! Das kann ich gefühlte 1000 mal brauchen. Toll!
Das mit dem Leerzeichen hätte ich wissen müssen, hatte ich schon mal. :-((
Danke! Georg :-)

318 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige