Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
VBA statt Formel kopieren
Mandy
Hi guys,
ich habe gerade erst angefangen, mich mit VBA anzufreunden, daher für Euch wahrscheinlich ne ganz simple Frage...: ;-)

Momentan habe ich eine Tabelle in der in J3 folgende Formel steht, die dann für den Rest der Spalte (soweit benötigt) nach unten kopiert wird:

=WENN(G3="";""WENN(ODER((G3="AT");(G3="BE");(G3="BG");(G3="CY");(G3="CZ");(G3="DE");(G3="DK"); (G3="EE");(G3="ES");(G3="FI");(G3="FR");(G3="GB");(G3="GI");(G3="GR");(G3="HU");(G3="IE");(G3="IT") ; (G3="LT");(G3="LU");(G3="LV");(G3="MT");(G3="NL");(G3="PL");(G3="PT");(G3="RO");(G3="SE"); (G3="SI"); (G3="SK");(G3="UK"));1;0))
Da die Datei teilweise für bis zu 20.000 Datensätze genutzt wird, macht das (neben anderen enthaltenen Formeln) die Datei entsprechend groß (mehrere MB).

Gibt es die Möglichkeit, anstelle einer Formel, die man kopiert, diese per Makro zu berechnen ?

Ich meine nicht, dass das Makro die Formel kopiert (das wäre easy), sondern dass das Makro eine solche Formel auf alle Einträge in Spalte G verwendet... ?
Bin Euch für jeden Tipp dankbar ! :-)
LG aus Dublin,
Mandy
AW: VBA statt Formel kopieren
Hajo_Zi
Hallo Mandy,
ich hätte vielleicht SVERWEIS() benutzt
Tabelle1
 GH
3   
4AT1
5Br0

verwendete Formeln
Zelle Formel Bereich
H3 =WENN(G3="";"";WENNFEHLER(SVERWEIS(G3;$A$16:$B$44;2;0);0)) 
H4 =WENN(G4="";"";WENNFEHLER(SVERWEIS(G4;$A$16:$B$44;2;0);0)) 
H5 =WENN(G5="";"";WENNFEHLER(SVERWEIS(G5;$A$16:$B$44;2;0);0)) 



Tabelle1
 AB
16AT1
17BE1
18BG1
19CY1
20CZ1
21DE1
22DK1
23EE1
24ES1
25FI1
26FR1
27GB1
28GI1
29GR1
30HU1
31IE1
32IT1
33LZ1
34LU1
35LV1
36MT1
37NL1
38PL1
39PT1
40RO1
41SE1
42SI1
43SK1
44UK1

Tabellendarstellung in Foren Version 5.38



Anzeige
AW: VBA statt Formel kopieren
Mandy
Hi Hajo,
danke ! :-)
Ja, das geht auch, aber dann muss ich trotzdem eine Formel von H3 bis H20000 kopieren, damit sie auch bei allen Datensätzen greift.
Das Problem dabei ist, dass ich mehrere solcher Spalten habe und das in Summe die Datei einfach riesen groß werden lässt.
Kann meine eine solche Formel nicht in VBA als Code definieren und sagen, wende das auf alle Einträge in Spalte G an und gib das Ergebnis (0 oder 1) z.B. in Spalte H aus ?
LG,
Mandy
AW: VBA statt Formel kopieren
Hajo_Zi
Hallo Mendy,
ich habe jetzt mal die Formel in 120.000 Zellen eingefügt und die Datei wurde um 1 MB größer, nicht mehrere MB.
Gruß Hajo
Anzeige
AW: VBA statt Formel kopieren
Mandy
Hi Hajo,
Tino hatte das, wonach ich gesucht habe - trotzdem vielen Dank ! :-)
Noch eine allgemeine Frage:
Ich sehe Antworten auf meinen Beitrag zwar online in Forum, bekomme aber keine Emails dazu, obwohl meine Emailadresse richtig angegeben ist (im Spam folder landet auch nichts).
Weisst Du, warum ?
LG,
Mandy
AW: VBA statt Formel kopieren
Hajo_Zi
Hallo Mandy,
es kann schon sein das di in dem Spm Ordner gelandet sind. Mal bei Deinem Provifer nachschauen.
Die Mailbenachrichtigung hast Du schon aktiviert im Forum?
Gruß Hajo
Anzeige
AW: VBA statt Formel kopieren
Mandy
Danke, Hajo ! :-)
Werd ich machen !
Kannst Du mir sagen, wie die Absender-Emailadresse bei Forumantworten auf meine Beiträge lautet ?
LG,
Mandy
AW: VBA statt Formel kopieren
Reinhard
Hallo Mandy,
info@herber.de
Der Betreff ist:
Nachricht Excel-Forum(Betreff)
Hinweis, dort steht der Betreff des Antworters, der kann ja völlig neu sein.
Gruß
reinhard
AW: VBA statt Formel kopieren
Mandy
Super - danke, Reinhard ! :-)
LG,
Mandy
AW: VBA statt Formel kopieren
Hajo_Zi
Hallo Mandy,
hans@herber.de
Gruß Hajo
AW: VBA statt Formel kopieren
Mandy
Danke, Hajo ! :-)
AW: VBA statt Formel kopieren
Reinhard
Hallo Hajo,
im Gegensatz zu dir frage ich ja hier an. Und Antworten darauf haben den Absender info@herber.de.
hans@herber.de ist wohl die Mailadresse von Hans selbst.
Gruß
Reinhard
Anzeige
AW: VBA statt Formel kopieren
Hajo_Zi
Hallo Mandy,
DDu solltest nicht in 2 Zweigen des Beitrages die Disskusion führen. Durch unsere Antworten ist der Beitrag aus offen raus. Ich stelle den Beitrag nicht auf offen da dieser Zweig meiner Meinung gelöst ist. Der andere Zweig ist aber wahrscheinlich noch offen. Der Beitrag steht aber nicht mehr in der Liste der offenen Beiträge.
Gruß Hajo
AW: VBA statt Formel kopieren
Mandy
Danke, Hajo - hab's gemerkt, dass das etwas Durcheinander gibt... ;-)
Sorry, werd's bei neuen Threads besser machen...!
LG,
Mandy
Anzeige
kürzere Formel
Erich
Hi,
vielleicht reicht auch eine dier Verkürzungen in I3:J3.
 GHIJ
3cy111
4eee00 

Formeln der Tabelle
ZelleFormel
H3=WENN(G3="";"";WENN(ODER((G3="AT"); (G3="BE"); (G3="BG"); (G3="CY"); (G3="CZ"); (G3="DE"); (G3="DK"); (G3="EE"); (G3="ES"); (G3="FI"); (G3="FR"); (G3="GB"); (G3="GI"); (G3="GR"); (G3="HU"); (G3="IE"); (G3="IT"); (G3="LT"); (G3="LU"); (G3="LV"); (G3="MT"); (G3="NL"); (G3="PL"); (G3="PT"); (G3="RO"); (G3="SE"); (G3="SI"); (G3="SK"); (G3="UK")); 1;0))
I3=WENN(G3="";"";--ISTZAHL(SUCHEN(G3&" ";"AT BE BG CY CZ DE DK EE ES FI FR GB GI GR HU IE IT LT LU LV MT NL PL PT RO SE SI SK UK ")))
J3=WENN(LÄNGE(G3)<>2;"";--ISTZAHL(--(REST(SUCHEN(G3;"ATBEBGCYCZDEDKEEESFIFRGBGIGRHUIEITLTLULVMTNLPLPTROSESISKUK"); 2)=1)))

Es bleibst natürlich die grundsätzliche Frage, ob du in der Tabelle besser Formeln oder besser nur Werte stehen hast.
Formeln sind eher geeignet, wenn sich Daten in Spalte G ändern können - die Werte werden dann automatisch angepasst.
Kann sich in G nichts ändern, reichen die Formelwerte aus. Dann kann man sie berechnen lassen und dann mit
"Kopieren - Inhalte einfügen - Nur Werte" die Formeln durch Werte ersetzen. Das entschlackt die Mappe.
Ob sich ein VBA-Einsatz rentiert, hängt von deinen Kenntnissen ab und davon, wie oft dieser Vorgang stattfinden muss.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: kürzere Formel
Mandy
Hi Erich,
vielen Dank auch an Dich ! :-)
Aus meiner Sicht wäre VBA wirklich das Beste hier, da die Datei täglich genutzt wird und jeden Tag komplett neue Werte importiert werden.
Kann man eine einfache Wenn-Dann-Formel mit einer Liste wie hier als VBA Code nutzen ?
Ich weiss, dass ist schwierig für Euch, wenn jemand noch so ganz am Anfang ist, aber ich wollte nun einfach mal damit anfangen...
LG,
Mandy
AW: VBA statt Formel kopieren
Tino
Hallo,
kannst mal diesen Code testen.
Geprüft wird ab G2 bis Ende in Spalte und ergebnis kommt in Spalte J.
Tabellennamen müsstest Du im Code noch anpassen.
Sub Berechnen()
Dim ArrayData, ArrayAusgabe()
Dim oDic As Object
Dim nCount As Long

Set oDic = CreateObject("Scripting.Dictionary")

With Sheets("Tabelle1") 'Tabelle anpassen 
    .Range("J2", .Cells(.Rows.Count, 10)).ClearContents
    
    With .Range("G2", .Cells(.Rows.Count, 7).End(xlUp))
        If .Cells(1, 1).Row > 1 Then
            FuellDictionary oDic
            ArrayData = .Cells.Resize(, 2)
            
            Redim Preserve ArrayData(1 To Ubound(ArrayData), 1 To 1)
            Redim Preserve ArrayAusgabe(1 To Ubound(ArrayData), 1 To 1)
                    
            For nCount = 1 To Ubound(ArrayData)
                If ArrayData(nCount, 1) <> "" Then
                    ArrayAusgabe(nCount, 1) = 0
                    If oDic.exists(ArrayData(nCount, 1)) Then
                        ArrayAusgabe(nCount, 1) = 1
                    End If
                End If
            Next nCount
            .Offset(0, 3) = ArrayAusgabe
        End If
    End With

End With
End Sub


Sub FuellDictionary(ByRef oDic As Object)
Dim ZeichenArray, varData
ZeichenArray = Array("AT", "BE", "BG", "CY", "CZ", "DE", "DK", _
               "EE", "ES", "FI", "FR", "GB", "GI", "GR", "HU", _
               "IE", "IT", "LT", "LU", "LV", "MT", "NL", "PL", _
               "PT", "RO", "SE", "SI", "SK", "UK")

For Each varData In ZeichenArray
    oDic(varData) = 0
Next

End Sub
Gruß Tino
Anzeige
AW: VBA statt Formel kopieren
Mandy
Hi Tino,
genau das, genial - vielen vielen Dank - that's brilliant !! :-)
Damit ich's auch verstehe, kannst Du mir noch sagen, warum der Part .Rows.Count einmal den Wert 10 und einmal den Wert 7 enthält ?
Ist das Dictionary hier so eine Art VBA-interne Ablage ?
LG from Dublin,
Mandy
AW: VBA statt Formel kopieren
Mandy
...kannst den ersten Teil meiner Frage löschen, hab's kapiert - das sind die Spaltennummern... ;-)
LG,
Mandy
AW: VBA statt Formel kopieren
Tino
Hallo,
sagen wir mal so ein Dictionary-Objekt ist eine Art Collection.
Jeder Eintrag hat einen Schlüssel und diesem Schlüssel kann man einen Wert zuweisen.
In Deinem Beispiel sind die Kürzel die Schlüssel und diesem weise ich einfach den Wert 0 zu,
weil wir in diesem Fall keinen Wert benötigen, könnte es auch ein anderer beliebiger Wert sein.
Dann wird eben geprüft ob der Wert aus G als Schlüssel im Dictionary-Objekt enthalten ist.
Gib mal im VBA in der Hilfe Dictionary ein, da bekommst Du einiges an Lesestoff.
Man könnte es auch anders lösen, aber diese Variante wird bei so vielen Daten die schnellste sein.
Gruß Tino
Anzeige
oder ohne Dictionary?
Erich
Hi Mandy und Tino,
das mit der Geschwindigkeit sehe ich noch nicht.
Testet doch mal, wie schnell das hier geht:

Sub RechneJ()
Dim arrG, arrK, gg As Long, lngK As Long, kk As Long
Dim arrAusgabe()
With Sheets("Tabelle1")                         'Tabelle anpassen
lngK = .Cells(.Rows.Count, 10).End(xlUp).Row
If lngK > 2 Then .Cells(2, 10).Resize(lngK - 1).ClearContents
With .Cells(2, 7).Resize(.Cells(.Rows.Count, 7).End(xlUp).Row - 1)
If .Cells(1, 1).Row > 1 Then
' Sort der Kürzel etwa nach Häufigkeit des Auftretens
arrK = Split("DE NL PL AT BE BG CY CZ DK EE ES FI FR GB" & _
" GI GR HU IE IT LT LU LV MT PT RO SE SI SK UK")
lngK = UBound(arrK)
arrG = .Value
ReDim arrAusgabe(1 To UBound(arrG))
For gg = 1 To UBound(arrG)
If arrG(gg, 1) = "" Then
arrAusgabe(gg) = ""
Else
For kk = 0 To lngK
If arrG(gg, 1) = arrK(kk) Then Exit For
Next kk
arrAusgabe(gg) = -(kk > lngK)
End If
Next gg
.Offset(0, 3) = Application.Transpose(arrAusgabe)
End If
End With
End With
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
stimmt kann man vernachlässigen...
Tino
Hallo,
bei 20000 Zeilen mit Daten
ohne Dictionary 0,234375
mit Dictionary 0,09375
Gruß Tino
AW: VBA statt Formel kopieren
Mandy
Hi Tino,
ich hätte noch eine Ergänzung - vielleicht kannst Du mir ja auch dabei noch helfen ? :-)
Auch die folgende Formel würde ich gerne durch VBA ersetzen und die Ergebnisse in Spalte K ausweisen - hast Du dazu auch eine Idee ?
=WENN(A1="";"";WENN((HEUTE()-A1)=0;1;(HEUTE()-A1)))
LG,
Mandy
AW: VBA statt Formel kopieren
Tino
Hallo,
müsste so funktionieren, kannst ja mal testen.
Tabelle wieder anpassen.
Da Deine Formel bei A1 beginnt, gehe ich davon aus, dass Deine Tabelle keine Überschrift hat.
Sub Beispiel()
Dim ArrayData(), ArrayAusgabe()
Dim nCount As Long, DateHeute As Long

With Sheets("Tabelle2")
    .Columns(11).ClearContents
    
    DateHeute = CLng(Date)
    
    ArrayData = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp)).Resize(, 2).Value2
    Redim Preserve ArrayAusgabe(1 To Ubound(ArrayData), 1 To 1)
    
    For nCount = 1 To Ubound(ArrayData)
        
        If DateHeute - ArrayData(nCount, 1) = 0 Then
            ArrayAusgabe(nCount, 1) = 1
        Else
            ArrayAusgabe(nCount, 1) = DateHeute - ArrayData(nCount, 1)
        End If
    Next nCount
    
    .Range("K1").Resize(Ubound(ArrayAusgabe), 1) = ArrayAusgabe
End With
End Sub
Gruß Tino
AW: VBA statt Formel kopieren
Mandy
Auch das klappt bestens - vielen vielen Dank für die gute und schnelle Hilfe !! :-)
LG,
Mandy
AW: VBA statt Formel kopieren
Daniel
Hi
wenn ich wie du daß Problem habe, daß zwar eine Formellösung existiert, aber die die Formel die Datei gross und träge macht, dann löse ich das so, daß ich die Formel per Makro eintrage und dann die Formel durch ihren Wert ersetze.
das mache ich aus folgenden Gründen:
1. bin ich zu faul, die Formel als Makro nachzuprogrammieren
2. ist die Formellösung meist schneller und einfacher (nicht immer, aber oft) als eine reine Makrolösung (zumindes muss die Makrolösung mit viel KnowHow und aufwand programmiert werden, um besser zu sein als die Formel).
wenn du dich beispielsweise für die Lösung von Haja mit dem SVerweis entscheiden solltest, würde das als Makro so aussehen:
With Range("J3:J" & Cells(Rows.Count, 7).End(xlup).Row)
.FormulaLocal = "=WENN(G3="""";"""";WENNFEHLER(SVERWEIS(G3;$A$16:$B$44;2;0);0))"
.Formula = .Value
End With

oder so:
With Range("J3:J" & Cells(Rows.Count, 7).End(xlup).Row)
.FormulaLocal = "=WENN(G3="""";"""";WENNFEHLER(SVERWEIS(G3;$A$16:$B$44;2;0);0))"
.copy
.PasteSpecial xlpastevalues
.Application.CutCopyMode = False
End With
die 2. Methode ist etwas schneller, setzt aber die Markierung auf den Zellbereich, was manchmal störend ist.
bei .FormulaLocal kannst du die Formel so verwenden, wie sie in der ersten Zelle des Zellbereichs steht, lediglich die Anführungszeichen, die zur Formel gehören, müssen doppelt eingegeben werden ("" statt ")
Gruß, Daniel
AW: VBA statt Formel kopieren
Mandy
Wow -- das war sozusagen meine Wunschvorstellung ! ;-)
Danke, Daniel - awesome !! :-)
Kannst Du mir das mit den Anführungszeichen für die 1. Methode noch ein bisschen genauer erklären, damit ich's auch selber anpassen kann ?
D.h, da wo ich normalerweise in der Formel 2x " verwende, setze ich es hier in VBA 4x ?
Der Code gilt hier automatisch für die ganze Spalte, richtig ?
Geht das mit allen Formeln oder gibt es grundsätzliche Einschränkungen ?
LG und nochmal vielen Dank für die Tipps,
Mandy
zu 1 / 3
Reinhard
Hallo Mandy,
ja. Wenn du sowas in Vba hast:
.FormulaLocal="=Excelformel"
dann bleiben diese zwei Gänsefüßchen so wie sie sind.
Alle Gänsefüßchen innerhalb der Excelformel schreibst du doppelt.
Gruß
Reinhard
AW: zu 1 / 3
Mandy
Super - vielen Dank nochmal für den tollen Tipp !!
LG,
Mandy
AW: VBA statt Formel kopieren
Daniel
HI
VBA muss ja irgenwie unterscheiden, ob ein Anführungszeichen im Code jetzt zum String gehört oder diesen begrenzt.
Daher werden Alle Anführungszeichen, die zum String gehören, doppelt eingegeben.
das gesagte geht prinzipell mit jeder Formel und gilt immer für den ganzen Bereich, der bei WITH angegeben ist. Die Formel muss immer für die erste Zelle des angegebenen Zellbereichs geschrieben werden.
Gruß Daniel
AW: VBA statt Formel kopieren
Mandy
Auch Dir vielen Dank für die tolle Hilfe hier - klasse, ist echt super so !! :-)
LG,
Mandy
AW: VBA statt Formel kopieren
Mandy
Hi Daniel,
ich hab Deinen Vorschlag aus der 1. Variante (.FormulaLocal...) jetzt mal mit ein paar völlig anderen Formeln durchgespielt und es hat bestens geklappt ! :-)
Eines hab ich dabei noch nicht verstanden:
Das Makro rechnet die Formel dann immer nur für die jeweils erste Zelle einer Spalte.
Was muss ich dort z.B. statt A1 eingeben, damit das automatisch für alle Einträge berechnet und entsprechend angezeigt wird ?
Wäre klasse, wenn Du mir das noch kurz erklären könntest !
LG,
Mandy
AW: sorry verstehe dein Problem nicht
Daniel
kannst du die Frage nochmal stellen und ggf ne beispieldatei mit dem Problem hochladen?
Gruß, Daniel
AW: sorry verstehe dein Problem nicht
Mandy
Danke, Daniel - klar ! :-)
Wenn ich Deinen Code als Makro laufen lasse, wird in Zelle J3 eine 0 oder 1 ausgewiesen, je nachdem, was in Zelle G3 steht.
Das ist genau, was ich wollte und funktioniert einwandfrei.
Da ich aber ja nicht nur in Zeile 3 einen Eintrag habe, sondern gegebenenfalls in Zeile 3 bis 20000, müsste das gleiche auch mit allen anderen Zeilen geschehen, in denen sich ein Eintrag in Spalte G befindet.
Hier wird bislang nichts ausgewiesen, da der Code nur auf Zeile 3 ausgelegt ist, er sollte die gleiche Formel aber entsprechend nach unten "kopieren" (also so, wie wenn man eine normale Formel aus einer Zelle nach unten kopiert).
Konnte ich es jetzt besser beschreiben ?
LG,
Mandy
AW: sorry verstehe dein Problem nicht
Daniel
HI
die Formel wird soweit nach unten eingetragen, wie in Spalte 7 (G) Werte stehen.
wenn die Spalte G leer ist, musst du ggf eine andere auswählen.
die Zeilennummer wird mit diesem Code ermittelt:
& Cells(Rows.Count, 7).End(xlup).Row)
Gruß, Daniel
AW: sorry verstehe dein Problem nicht
Mike
...jetzt hab ich's kapiert - ich hatte die Spalten geändert und dadurch war Spalte 7 bei mir nun immer leer...!
Sorry, dass ich nachfragen musste, aber so versteh ich jetzt auch, was da abläuft...
Danke nochmal, Daniel - ist absolut genial so !
LG und schönen Abend Dir,
Mandy

316 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige