Live-Forum - Die aktuellen Beiträge
Datum
Titel
18.04.2024 21:09:11
4 Spalten aus smarter Tabelle in andere smarte Tab. kopieren
18.04.2024 20:45:35
Worksheet_Activate für alle Sheets
18.04.2024 19:30:20
VBA - Tabelle mit Mannschaftsergebnissen sortieren
18.04.2024 18:04:29
Formel fehler Wert#
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1220to1224
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1220to1224
1220to1224
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Formeln per VBA ändern?

Formeln per VBA ändern?
Sergej
Hallo Leute,
im habe im einem Arbeitsblatt die Formeln im Zellenbereich D7:I11 eingetragen.
Beispiel:
---------
D7=WENN(ODER([Bilanz.xlsx]Filialen!$H3="";[Bilanz.xlsx]Filialen!$I3="");"";[Bilanz.xlsx]Filialen!$H3)
E7=WENN(ODER([Bilanz.xlsx]Filialen!$I3="";[Bilanz.xlsx]Filialen!$I3="");"";[Bilanz.xlsx]Filialen!$I3)
D8=WENN(ODER([Bilanz.xlsx]Filialen!$H4="";[Bilanz.xlsx]Filialen!$I4="");"";[Bilanz.xlsx]Filialen!$H4)
E8=WENN(ODER([Bilanz.xlsx]Filialen!$I4="";[Bilanz.xlsx]Filialen!$I4="");"";[Bilanz.xlsx]Filialen!$I4)
D9=WENN(ODER([Bilanz.xlsx]Filialen!$H5="";[Bilanz.xlsx]Filialen!$I5="");"";[Bilanz.xlsx]Filialen!$H5)
E9=WENN(ODER([Bilanz.xlsx]Filialen!$I5="";[Bilanz.xlsx]Filialen!$I5="");"";[Bilanz.xlsx]Filialen!$I5)
D10=WENN(ODER([Bilanz.xlsx]Filialen!$H6="";[Bilanz.xlsx]Filialen!$I6="");"";[Bilanz.xlsx]Filialen!$H6)
E10=WENN(ODER([Bilanz.xlsx]Filialen!$I6="";[Bilanz.xlsx]Filialen!$I6="");"";[Bilanz.xlsx]Filialen!$I6)
D11=WENN(ODER([Bilanz.xlsx]Filialen!$H7="";[Bilanz.xlsx]Filialen!$I7="");"";[Bilanz.xlsx]Filialen!$H7)
E11=WENN(ODER([Bilanz.xlsx]Filialen!$I7="";[Bilanz.xlsx]Filialen!$I7="");"";[Bilanz.xlsx]Filialen!$I7)
D12=WENN(ODER([Bilanz.xlsx]Filialen!$H8="";[Bilanz.xlsx]Filialen!$I8="");"";[Bilanz.xlsx]Filialen!$H8)
E12=WENN(ODER([Bilanz.xlsx]Filialen!$I8="";[Bilanz.xlsx]Filialen!$I8="");"";[Bilanz.xlsx]Filialen!$I8)
D13=WENN(ODER([Bilanz.xlsx]Filialen!$H9="";[Bilanz.xlsx]Filialen!$I9="");"";[Bilanz.xlsx]Filialen!$H9)
E13=WENN(ODER([Bilanz.xlsx]Filialen!$I9="";[Bilanz.xlsx]Filialen!$I9="");"";[Bilanz.xlsx]Filialen!$I9)
D14=WENN(ODER([Bilanz.xlsx]Filialen!$H10="";[Bilanz.xlsx]Filialen!$I10="");"";[Bilanz.xlsx]Filialen!$H10)
E14=WENN(ODER([Bilanz.xlsx]Filialen!$I10="";[Bilanz.xlsx]Filialen!$I10="");"";[Bilanz.xlsx]Filialen!$I10)
D15=WENN(ODER([Bilanz.xlsx]Filialen!$H11="";[Bilanz.xlsx]Filialen!$I11="");"";[Bilanz.xlsx]Filialen!$H11)
E15=WENN(ODER([Bilanz.xlsx]Filialen!$I11="";[Bilanz.xlsx]Filialen!$I11="");"";[Bilanz.xlsx]Filialen!$I11)
Die Formeln (D7:I15) habe ich in vielen anderen Arbeitsblättern auch im Zellenbereich D7:I15 kopiert.
Mein Problem ist jetzt, dass die Zeilen, die in Formeln eingetragen sind in kopierten Arbeitsblätter anders sein sollen.
Beispiel nachher für die Zeile 7 im kopierten Arbeitsblatt:
------------------------------------------------------------
D7=WENN(ODER([Bilanz.xlsx]Filialen!$H14="";[Bilanz.xlsx]Filialen!$I14="");"";[Bilanz.xlsx]Filialen!$H14)
E7=WENN(ODER([Bilanz.xlsx]Filialen!$I14="";[Bilanz.xlsx]Filialen!$I14="");"";[Bilanz.xlsx]Filialen!$I14)
Aus Zeile 3 (in der Formel) ist eine 14 geworden. Die 14 setzt sich wie folgt zusammen:
Es soll immer geschaut werden, was in der Zelle A5 aktives Blattes steht. Nehmen wir an, es steht eine "2".
Also 2 x 11 (immer fix) - 8 (immer fix) = 14
Wenn die Formel in Zeile 7 für die Spalten D und E per VBA geändert ist, dann würde ich gerne diese bis Zeile 15 runter kopieren. Dann wird sich die Zeile in der Formel beim runterkopieren, um eins erhöhen.
Das ganze würde ich per VBA ändern.
Wie mache ich das bitte per VBA?
Danke vorab!
Grüße,
Sergej

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
Allgemeinere Formeln
23.07.2011 12:20:43
Erich
Hi Sergej,
die Formeln könntest du etwas kürzer aufschreiben, besonders in Spalte E (wie in Zeile 9).
(Das "[Bilanz.xlsx]" hab ich hier mal weggelassen.)
Du sparst dir das VBA vielleicht, wenn du die Formel etwas variabler schreibst - wie in den Zeilen 7:8:
 ADE
51  
6   
7 01111
8 2222 
9 033333

Formeln der Tabelle
ZelleFormel
D7=WENN(INDEX(Filialen!H:H;$A$5*11-15+ZEILE())&INDEX(Filialen!I:I;$A$5*11-15+ZEILE())="";"";INDEX(Filialen!H:H;$A$5*11-15+ZEILE()))
E7=WENN(INDEX(Filialen!I:I;$A$5*11-15+ZEILE())="";"";INDEX(Filialen!I:I;$A$5*11-15+ZEILE()))
D8=WENN(INDEX(Filialen!H:H;$A$5*11-15+ZEILE())&INDEX(Filialen!I:I;$A$5*11-15+ZEILE())="";"";INDEX(Filialen!H:H;$A$5*11-15+ZEILE()))
E8=WENN(INDEX(Filialen!I:I;$A$5*11-15+ZEILE())="";"";INDEX(Filialen!I:I;$A$5*11-15+ZEILE()))
D9=WENN(Filialen!H5&Filialen!I5="";"";Filialen!H5)
E9=WENN(Filialen!I5="";"";Filialen!I5)

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Formeln per VBA ändern?
23.07.2011 13:55:56
fcs
Hallo Sergej,
den Vorschlag von Erich bezüglich der INDEX-Funktion solltest du anwenden.
Erichs Vorschlag bezüglich des Ersetzens der ODER-Prüfung durch "&"-Verknüpfung der beiden Textteile ist nicht korrekt. Der würde nur für eine UND-Prüfung auf leere Zellen funktionieren.
In der Spalte E prüfst du wie von Erich angemerkt in der ODER-Prüfung 2-mal die gleiche Zelle in Spalte I, ist das ein Tippfehler? ggf. sollte dann die Formel vereinfacht werden. Mein Makro läßt die 2. Prüfung schon weg.
Nachfolgend Makros, die die Formeln im Bereich D7:E15 neu schreiben. Sowohl in deiner alten Version
Als auch mit Erichs INDEX-Vorschlag.
Gruß
Franz 
Sub Bilanz_Formel_D7_E15_INDEX()
'Formel im R1C1 Format erzeugen und einfügen - funktioniert mit allen Sprachversionen _
und Einstellungen für Betriebssystem und/oder Excel
With ActiveSheet
.Range("D7:D15").FormulaR1C1 = "=IF(OR(INDEX([Bilanz.xlsx]Filialen!C8,R5C1*11-15+ROW())," _
& "INDEX([Bilanz.xlsx]Filialen!C9,R5C1*11-15+ROW())=""""),""""," _
& "INDEX([Bilanz.xlsx]Filialen!C8,R5C1*11-15+ROW()))"
.Range("E7:E15").FormulaR1C1 = "=IF(INDEX([Bilanz.xlsx]Filialen!C9,R5C1*11-15+ROW())="""","  _
_
& """"",INDEX([Bilanz.xlsx]Filialen!C9,R5C1*11-15+ROW()))"
End With
End Sub
Sub Bilanz_Formel_D7_E15()
'Formel im R1C1 Format erzeugen und einfügen - funktioniert mit allen Sprachversionen _
und Einstellungen für Betriebssystem und/oder Excel
Dim Zeile As Long
With ActiveSheet
Zeile = .Range("A5").Value * 11 - 8
'Zeile in Original-Formel (A5 = 1):  3
'Relativer Wert in Original-Formel: -4
'Relativer Wert für neuen Zeilenbezug
Zeile = -4 + Zeile - 3
.Range("D7:D15").FormulaR1C1 = "=IF(OR([Bilanz.xlsx]Filialen!R[" & Zeile _
& "]C8="""",[Bilanz.xlsx]Filialen!R[" & Zeile _
& "]C9=""""),"""",[Bilanz.xlsx]Filialen!R[" & Zeile & "]C8)"
.Range("E7:E15").FormulaR1C1 = "=IF([Bilanz.xlsx]Filialen!R[" & Zeile _
& "]C9="""","""",[Bilanz.xlsx]Filialen!R[" & Zeile & "]C9)"
End With
End Sub
Sub Bilanz_Formel_D7_E15_Var()
'Formel im lokalen Format erzeugen und einfügen - funktioniert ggf. nur mit Deutschen _
Versionen und Einstellungen für Betriebssysten und/oder Excel
Dim Zeile As Long
With ActiveSheet
Zeile = .Range("A5").Value * 11 - 8
'Zeile in Original-Formel (A5 = 1):  3
.Range("D7:D15").FormulaLocal = "=WENN(ODER([Bilanz.xlsx]Filialen!$H" & Zeile _
& "="""";[Bilanz.xlsx]Filialen!$I" & Zeile _
& "="""");"""";[Bilanz.xlsx]Filialen!$H" & Zeile & ")"
.Range("E7:E15").FormulaLocal = "=WENN([Bilanz.xlsx]Filialen!$I" & Zeile _
& "="""";"""";[Bilanz.xlsx]Filialen!$I" & Zeile & ")"
End With
End Sub

Anzeige
AW: Formeln per VBA ändern?
23.07.2011 19:55:51
Sergej
Hallo zusammen,
@Erich: Erstmal Danke für die Formeln!!!
Deine D7 und E7 Formel habe ich getestet und es funktionieren.
Ich habe, dann in die Formel noch "[Bilanz.xlsx]" und die Prüfung (wenn oder H3 oder I3 leer, dann nichts) eingebaut, bekomme dann die Meldung:
"Ungültiger Bezug. Diese Dateiversion darf keine Formeln enthalten, die auf Zellen verweisen, die außerhalb der Arbeitsblattgröße von 256 Spalten (Spalte IW oder höher) oder 65536 Zeilen liegen."
Die Datei, in der Formel eingefügt wird, liegt im XLS-Format und die Bilanzdatei im XLSX-Format.
Liegt das daran, oder weil die Formel zu lang ist?
@Franz: Erstmal Danke auch für die VBA-Routinen!!!
Dieses Makro funktioniert bei mir nicht: "Bilanz_Formel_D7_E15_INDEX".
Es kommt diese Meldung: Laufzeitfehler 1004 - Anwendungs- oder objektdefinierter Fehler.
Die anderen beiden Makros funktionieren perfekt!
Viele Grüße,
Sergej
Anzeige
Formeln variabel und per VBA
24.07.2011 00:22:46
Erich
Hi Sergej,
meine Formeln in Zeile 7 sehen jetzt so aus (einschließlich der Prüfung: wenn oder H3 oder I3 leer, dann nichts):
 ADE
51  
6   
73a1111

Formeln der Tabelle
ZelleFormel
A7=$A$5*11-15+ZEILE()
D7=WENN(ODER(INDEX([Bilanz.xlsx]Filialen!$H$1:$H$65536;$A$5*11-15+ZEILE())=""; INDEX([Bilanz.xlsx]Filialen!$I$1:$I$65536;$A$5*11-15+ZEILE())=""); ""; INDEX([Bilanz.xlsx]Filialen!$H$1:$H$65536;$A$5*11-15+ZEILE()))
E7=WENN(INDEX([Bilanz.xlsx]Filialen!$I$1:$I$65536;$A$5*11-15+ZEILE())="";""; INDEX([Bilanz.xlsx]Filialen!$I$1:$I$65536;$A$5*11-15+ZEILE()))

Und mit dieser Prozedur wercden die Formeln in den Bereich D7:E15 geschrieben: 

Sub Bilanz_Formel_D7_E15_INDEX()
'Formel im R1C1 Format erzeugen und einfügen - funktioniert mit allen Sprachversionen _
und Einstellungen für Betriebssystem und/oder Excel
With ActiveSheet
.Range("D7:D15").FormulaR1C1 = _
"=IF(OR(INDEX([Bilanz.xlsx]Filialen!R1C8:R65536C8,R5C1*11-15+ROW())=""""," _
& " INDEX([Bilanz.xlsx]Filialen!R1C9:R65536C9,R5C1*11-15+ROW())=""""),""""," _
& " INDEX([Bilanz.xlsx]Filialen!R1C8:R65536C8,R5C1*11-15+ROW()))"
.Range("E7:E15").FormulaR1C1 = _
"=IF(INDEX([Bilanz.xlsx]Filialen!R1C9:R65536C9,R5C1*11-15+ROW())="""",""""," _
& " INDEX([Bilanz.xlsx]Filialen!R1C9:R65536C9,R5C1*11-15+ROW()))"
End With
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: Formeln variabel und per VBA
24.07.2011 00:24:51
Sergej
Vielen Dank Erich!!!!

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige