Adressbereiche in Formeln "zerstört"
10.02.2021 16:27:31
Vanessa
ich hoffe, ihr könnt und mögt mir bei meinem Problem weiterhelfen.
Normalerweise komme ich ganz gut mit VBA zurecht - ich habe im November 2018 damit angefangen (Google, vieles aus diesem Forum & Makrorekorder).
Soweit zu mir. Nun zur Problematik, die mir graue Haare bereitet ..
Ich habe eine Budgetplantabelle erstellt, welche vier vierschiedene Imports realisiert.
Dies läuft im VBA so: Mappe im Hintergrund öffnen, die Daten aus dem Quellarbeitsblatt kopieren und in das Zielarbeitsblatt einfügen. Dies funktioniert auch reibungslos.
Zusätzlich durchsuche ich in einigen der Imports die erste Zeile nach Spaltenüberschriften, speichere die Spaltennummer mit dem gefundenen Wert in einer Variable ab und schneide und verschiebe diese dann so:
Sub key_ind_search(ByRef keyname As String)
Dim searchrrng As Range 'Bereich, welcher durchsucht wird
Dim iCol As Integer 'Spaltenzahl (Rückgabewert aus Suche)
'Durchsuche die erste Zeile/die Überschriften nach dem Wert "keyname"
Range("A1").EntireRow.Select
Set searchrng = Selection
iCol = Application.WorksheetFunction.Match(keyname, searchrng, False)
'Wähle die Spalte mit der gesuchten Überschrift aus..
Cells(1, iCol).EntireColumn.Select
'..schneide sie aus und füge sie vorn wieder ein
Selection.Cut
Columns("A:A").Select
Selection.Insert Shift:=xlToRight
End Sub
Dies mache ich, um SVERWEISe zu realisieren und den Key Indicator nach vorn zu setzen. So weit, so gut.
Die Daten verarbeite ich zum Teil auch mit der SUMMEWENN(S)-Funktion(en) weiter und hier passiert es: Der Adressbereich wird, ich vermute durch das Versetzen, zerstört.
So wird beispielsweise aus dieser Formel ...
=WENNFEHLER(SUMMEWENNS('#Import PPMS'!$BD$2:$BD$3000;'#Import PPMS'!$A$2:$A$3000;Übersicht!$F$3; '#Import PPMS'!$AA$2:$AA$3000;"Abteilung A");"Keine Daten")
... diese Formel:
=WENNFEHLER(SUMMEWENNS('#Import PPMS'!$BD$2:$BD$3000;'#Import PPMS'!$B$2:$B$3000;Übersicht!$F$3; '#Import PPMS'!$AA$2:$AA$3000;"Abteilung A");"Keine Daten")
Dies passiert bei mehreren Formeln. Bei den SVERWEISen konnte ich dies mittels VBA abfangen (WsF.VLookup), bei SUMMEWENN(S) scheint es schwieriger/steige ich nicht durch die Syntax durch.
Ich habe es alternativ mit .Formula und Evaluate versucht und bin gescheitert. Eine weitere Idee ist es, dass ich die automatische Formelberechnung vor dem Import/der Spaltenumsetzung kurzzeitig deaktiviere und erst danach wieder aktiviere.
Wäre jemand so lieb, mir für die Idee die Befehle an die Hand zu geben? Der Makrorekorder hilft bei Einstellungen ja nicht. Und: Hat jemand eine alternative Idee? Übersehe ich etwas?
Ich bin für JEDE Antwort absolut dankbar. Bleibt gesund!
Liebe Grüße,
Vanessa
P.S.: Ich hätte gerne eine Beispielmappe angehangen, jedoch geht das aufgrund der Vielzahl sensibler Daten nicht.