AW: an Michael:Berechnungen für Spalten und Zeilen
20.01.2016 00:03:27
Michael
Hallo Andi,
die Datei bringt bei mir einen XML-Fehler. Wahrscheinlich hattest Du irgendwelche exteren Bezüge drin, die jetzt natürlich weg sind.
Aber egal: ich mußte erst einmal suchen, wo die Formel denn nun eigentlich steht.
Nur, damit wir nicht aneinander vorbeireden. Die oben gepostete Formel steht ...
a) einmal im Blatt "Kurzzeichen", und zwar in Zelle A27 (mit führendem "'")
b) noch einmal im Blatt "Stromverbrauch", und zwar in F90
Also scheint a) so etwas wie eine Kopiervorlage zu sein, die Du dann nach b) kopierst, dort das "'" entfernst und schließlich auf den kompletten Bereich F90 bis Q166 kopierst.
Das Kopieren machst Du natürlich nur, wenn sich an den Formeln was ändert...
Habe ich das richtig interpretiert?
Wenn ich mal davon ausgehe, daß es so ist, gibt es dazu zu sagen:
- es gibt eine max. Verschachtelungstiefe bei WENN; die war mal bei 8, ich weiß nicht, wie es aktuell ist.
- in den allermeisten Fällen trifft keines der Kriterien zu, d.h. die Formel übernimmt nur den obigen Wert (also F6)
Zusammen also wäre es besser und würde es Excel weniger ausbremsen, wenn man die Formeln nur dahin übernehmen würde, wo sie auch eingesetzt werden.
Also, mal sehen.
So. Im Blatt mit den Kürzeln habe ich in Spalte C eine Formel eingefügt, die die zu der jeweiligen Formel gehörige Zeilennummer heraussucht. Diese wird dann im Makro verwendet, um die Formel in die jeweilige Zeile zu kopieren.
Das Makro sieht so aus:
Sub FormelKopieren()
Dim Kurz_A27 As String ' Formel im Blatt "Kurzzeichen" in A27
Dim wohin As Variant ' das wird dann ein sogenanntes "Array"
Dim maxZeilen As Long
Const sv_Anfang = 90 ' 1. Kurzzeichen in "Tabelle2" des StromVerbrauchs
Dim sv_Ende As Long, i As Long, j As Long
Dim wsv As Worksheet
Dim t1 As Single
Set wsv = tabStromverbrauch
maxZeilen = Sheets("Kurzzeichen").Range("A" & Rows.Count).End(xlUp).Row
Kurz_A27 = Replace(Sheets("Kurzzeichen").Range("A" & maxZeilen), Chr(10), "")
maxZeilen = maxZeilen - 1
' -1, weil die Formel, die derzeit in A27 steht, nicht dabei
' sein soll - in der Liste "wohin" mit den Kurzzeichen
wohin = Sheets("Kurzzeichen").Range("A2:C" & maxZeilen)
' alle vorhandenen Kurzzeichen einlesen ...
'... und leere Zeilen raussortieren:
j = 0
For i = 1 To UBound(wohin, 1)
If wohin(i, 3) "" Then
j = j + 1
wohin(j, 3) = wohin(i, 3)
End If
Next
MsgBox "Zeilen gesamt: " & i & " Zeilen mit Nummern: " & j
t1 = Timer ' wie lange wird es wohl dauern?
' Jetzt geht's zur Sache: **************************
' 1. Kopieren aller Daten
wsv.Range("F6:Q82").Copy wsv.Range("F90")
' 2. *einmaliges* Einfügen der Formel in F90:
wsv.Range("F90").FormulaLocal = Kurz_A27
' 3. anschließendes Kopieren, aber nur in die benötigten Zeilen:
For i = 1 To j
wsv.Range("F90").Copy wsv.Range("F" & wohin(i, 3)).Resize(, 12)
Next
MsgBox "erledigt in " & (Timer - t1) * 1000 & " Millisekunden"
End Sub
und läuft gut fix.
Du hattest den Stromverbrauch ja schon kopiert; ich habe eine weitere Kopie angelegt und *vor* dem Test des Makros im Bereich F90 bis Q166 eine Verifizierung eingebaut (hier für F90),
='Stromverbrauch (2)'!F90=Stromverbrauch!F90
die die Werte vergleicht.
Sie stimmen auch *nach* dem Makroaufruf noch überein: paßt!
Ich bin nicht ganz glücklich mit der Lösung, denn Du mußt die Formel in A27 noch händisch anpassen, und eigentlich würden Teilformeln, die man zu jedem einzelnen Kürzel hinterlegt, genügen...
Aber Du solltest damit einen guten Schritt weiterkommen.
Die Datei: https://www.herber.de/bbs/user/102895.xlsm
Schöne Grüße,
Michael