Excel-Formel per VBA in eine Zelle schreiben?

Hallo,

ich habe eine Tabelle mit 1934 Zeilen und 6 Spalten mit diversen Werten. Am Ende dieser Tabelle möchte ich ein paar statistiche Auswertungen einfügen und habe ein Problem folgende Formel per VBA-Makro in eine Leerzeile einzufügen:

'=ZÄHLENWENNS(F2:F1934;"=0")+ZÄHLENWENNS(F2:F1934;"=1")

Schreibe ich diese Formel manuell in die Zelle, summiert sie was sie zählen soll!

Der Makro liefert mir einen Laufzeitfehler 1004 und ich weiß nicht warum...

FORM01A = FRST_F & ":" & LAST_F & ";""""=0"""""

FORM01B = FRST_F & ":" & LAST_F & ";""""=1"""""

FORM01 = "=ZÄHLENWENNS(" & FORM01A & ")" & "+ZÄHLENWENNS(" & FORM01B & ")"

Cells(neu, 1).EntireRow.Insert

Cells(neu, 1) = "Anzahl aktive MGL in " & xGMD

Cells(neu, 2) = FORM01

Ich vermute, dass es an den Hochkommata liegt. Hat jemand eine Idee?

Gruß jumo

5 Antworten

IchMalWiederXY

Von gutefrage auf Grund seines Wissens auf einem Fachgebiet ausgezeichneter Nutzer

VBA

29.03.2020, 12:42

Hallo, anstatt die Formel in die Zelle zu schreiebn könntest du auch direkt das Ergebnis reinschreiben.
=ZÄHLENWENNS(F2:F1934;"=0")+ZÄHLENWENNS(F2:F1934;"=1")
Range("A1").Value = Worksheetfunction.Countifs(Worksheets("Tab1").Range("F2:F1934"),"=0")+Worksheetfunction.Countifs(Worksheets("Tab1").Range("F2:F1934"),"=1")
Vorteil. Excel muss nicht Immer wieder dieses Ergebnis rechnen=>Zeitersparnis.
Wenn die ähnliche Formel 1000 fach in dein Sheet schreibst wird es sehr langsam.

Hannes62a

28.03.2020, 20:15

Hast Du Dir den String mal ausgeben lassen? Z.B. mit debug.print ins Direktfenster?

Die Formel musst Du der Eigenschaft Cells(neu.2).Formula zuweisen. Ich habe mal die Erfahrung gemacht, dass man zum Einfügen der Formel die englischen Bezeichnungen der Tabellenfunktionen verwenden muss. Es gibt zwar FormulaLocal, aber dass hat damals nicht ordentlich funktioniert.

Viel Erfolg

Hannes

karlericht

Beitragsersteller

29.03.2020, 12:12

Hallo, Oubyi hatte denrichtigen Tipp!

Oubyi, UserMod Light

28.03.2020, 21:04

Versuche es erstmal mit:

Cells(neu, 2).FormulaLocal = FORM01

Ansonsten hilft Dir vielleicht weiter, was ich mir hier zusammengeschrieben habe um Deinen Code auseinander nehmen zu können. DAS läuft.

Dim FORM01A As String, FORM01B As String, FORM01 As String
Dim neu As Long
neu = 3
Dim FRST_F As String, LAST_F As String
FRST_F = "E1"
LAST_F = "E10"

FORM01A = FRST_F & ":" & LAST_F & ";0"
Debug.Print FORM01A

FORM01B = FRST_F & ":" & LAST_F & ";1"
Debug.Print FORM01B

FORM01 = "=ZÄHLENWENNS(" & FORM01A & ")" & "+ZÄHLENWENNS(" & FORM01B & ")"
Debug.Print FORM01

Cells(neu, 1).EntireRow.Insert

Cells(neu, 1) = "Anzahl aktive MGL in " & xGMD

Cells(neu, 2).FormulaLocal = FORM01

karlericht

29.03.2020, 12:10

Hallo Oubyi,

Super! Ich habe mich krampfhaft an die Hochkommata gehalten, da diese mir aus einem anderen Beispiel mit ZÄHLENWENNS vorgegeben wurden. Die sind auch irgendwann notwendig um anzuzeigen, dass nach einem 2. Hochkomma der String nicht zu Ende ist sondern das Hochkomma Teil des Strings ist...

Auch das Gleichheitszeichen (=) konnte man problemlos weglassen...

Speichere ich diese Formel (ZÄHLENWENNS) manuelle in einer Zelle, funktioniert sie ja mit "=" und Kochkommata?!?!?!

Was mich aber trotzdem irrritiert ist die Tatsache, dass man im Web Beiträge findet die "ZÄHLENWENNS" und solche, die "ZÄHLENWENN" beschreiben...

Nohmals herzlichen Dank und bleib gesund!

jumo

Oubyi, UserMod Light

29.03.2020, 13:01

Gern geschehen 😊.
Dir auch alles Gute.

P.S.: Wenn Du auf eine Antwort reagierst, dann am besten als Kommentar zu dieser Antwort (wie bei Hannes) und nicht als eigene Antwort auf Deine Frage wie hier.
Auf den Kommentar bekommt der Antworter automatisch einen Hinweis, auf Deine Antwort nicht.

beelee

28.03.2020, 20:27

Wenn ich deine Frage richtig verstanden habe, schreibst du in Makro deutsche Formel? Versuchs mal, die englische "Variante". Nötigenfalls benutze die Makroaufzeichnung und adaptiere dann entsprechend

karlericht

Beitragsersteller

29.03.2020, 12:11

Hallo, Oubyi hatte denrichtigen Tipp!

Ähnliche Beiträge

Tabellenname aus Zelle mit VBA?

Hallo zusammen. Ich habe eine Tabelle mit mehreren Blättern und möchte mittels Makro die Tabellenblätter umbennen. Problematisch ist nur die Variabilität der Daten.

Beispiel

ich befinde mich in der Tabelle1, wo auch die Makro-Schaltfläche ist
in Tabelle2, Zelle A1 steht welche Tabelle (Tabelle4) einen neuen Namen erhalten soll
in Tabelle3, Zelle A1 steht der Namen den die Tabelle bekommen soll (Test)

Ich finde leider keine Lösung wie ich mit VBA ein Worksheet anspreche welches in Tabelle2, Zelle A1 definiert ist und dieses mit dem Namen aus Tabelle3, Zelle A1 belege.

danke im Vorraus für eure Unterstützung

...zum Beitrag

Excel VBA - Wert auslesen?

Mein Problem:

Mit dem ersten Makro wird ein Wert in Zelle A1 geschrieben.

In einem weiteren Makro will ich nun diesen Wert auslesen mit Worksheets("Blatt1").Cells(1, 1).Value = i. Wenn ich das mache steht danach in Zelle A1 aber eine 0 und nicht mehr der vorige Wert. Was ist falsch?

...zum Beitrag

Laufzeitfehler 429 in VBA (Excel Makro)?

Hallo!

Zurzeit versuche ich mich an VBA. Ich habe ein Makro erstellt, dass aus einer Excel Tabelle z.B. eine Rechnung erstellt ( durch befüllen der Texftfelder einer Vorlage )

Nun wollte ich das Makro so umändern, dass automatisch der richtige Pfad zum Speichern bzw Öffnen der Vorlage, für den jeweiligen Windows Nutzer benutzt wird. ( Das habe ich mithilfe von Workbook.Path gemacht.

Allerdings gibt er mir jetzt immer Laufzeitfehler 429 aus und ich weiß nicht was zu tun ist.

Bitte helft mir. ( Office und Word sind up to date )

Grüße

Chris

Hier mein Code: https://paste2.org/E8ZmvBXk

...zum Beitrag

Kopieren und auf andere Tabelle einfügen per VBA

Hei Leute, habe in Tabelle 3 im Bereich B5:C30 Texte stehen die ich ab der ersten freien Zelle in der Tabelle 1 der Spalte A einfügen muss. Dies muss ich ca. 100 mal machen, daher möchte ich es nicht von Hand machen. Habe schon selber probiert aber irgend etwas ist falsch, das Makro kopiert geht in die freie Zelle Tabelle1 Spalte A , aber fügt nicht ein. Kommt der Fehler Laufzeitfehler.

Sub umwechseln()

' umwechseln Makro

Dim s As String Dim i As Long

Range("A5:A29").Select
Application.CutCopyMode = False
Selection.Copy
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B5:C30").Select
 Sheets("Tabelle1").Select
    With ActiveSheet
i = 0
Do
    i = i + 1
    s = Cells(i, "A")
    If Len(s) = 0 Then
        Cells(i, "A").Select
        
        Exit Do
    End If
Loop While i < 65535End With   Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False Sheets("Tabelle3").Select
Range("E10").Select

End Sub

Sorry wenn das nicht gut dargestellt ist...weis nicht wie man aus VBA kopiert und hier in welchen Format einfügt.

Gruß Monkee

...zum Beitrag

Excel VBA - Zelle auswählen, dann F2

Hallo erneut Excel-Freunde,

mein Titel verrät es bereits. Eine Zelle soll markiert werden, daraufhin soll "F2" und dann "Enter" gedrückt werden.

Mein Makro sieht bisher so aus:

'Ans Ende der Tabelle springen

ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Select

ActiveCell.Offset(1, 0).Select

' Eingabeaufforderung 1 Datum

Eingabe = InputBox("Bitte Datum angeben, wann die Ware eingetroffen ist!" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & _ "Bitte schreiben Sie in: tt:mm:jjjj" & Chr(13) & Chr(10) & _ "" & _ "", _ "Zugang [Datum]", ActiveSheet.Range("B1") _ & "_", 7000, 5000)

If StrPtr(Eingabe) = 0 Then ' MsgBox ("Die Schaltfläche Abbrechen wurde gewählt. Der Vorgang wird abgebrochen.") End Else

End If

ActiveCell.Offset(0, 0).Value = Eingabe

Wie man sieht, sucht er sich selbst die letzte Zelle in meiner Tabelle um dort mit einer Eingabeaufforderung das Datum anzugeben.

Nun möchte ich als nächsten Schritt, genau diese Zelle, wo man eben das Datum eingegeben hat mittels dem Makro da oben, ... das er diese Zelle auswählt, "F2" und dann "Enter" drückt.

Alle Versuche dies zu bewerkstelligen, schlugen bisher fehl.

Habt ihr eine Idee?

Viele Grüße,

David :) ... Pixelschubster :D

...zum Beitrag

EXCEL Makro @ vor Formel?

Die Formel die Kopiert werden soll ist:
=SVERWEIS(B3;Data!A1:C26;2;FALSCH)*F3

Die Kopierte Formel erscheint so:
=@SVERWEIS($B4; Data!$A$1:$C$26; 2; FALSCH)*F4

Mein VBA Makro ist :

Sub HinzufuegenUndKopieren()

Dim WertAusSpalte2 As Variant

WertAusSpalte2 = Cells(3, 2).Value

Rows(4).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Cells(4, 2).Value = WertAusSpalte2

Cells(4, 5).Formula = "=$B$4"

Cells(4, 6).Value = Cells(3, 6).Value

Cells(4, 7).Formula = "=SVERWEIS($B4, Data!$A$1:$C$26, 2, FALSE)*F4"

End Sub

Wie bekomme ich es hin, dass die Zeile ohne @ Kopiert wird ?.

...zum Beitrag

Kann mir jemand bei diesem Makro eine Schleife einbauen?

ich benötige dringend Hilfe bei diesem Makro!

ich habe dieses Makro von einem Mitglied von gf.

dieses Makro benötigt dringend eine Schleife

von Zeile 1 To 6000

also Zeile 1 ist SUMME(ZÄHLENWENN($A1:$F1;$L:$Q))

und Zeile 2 wäre dann $A2:$F2

und das Ganze dann bis Zeile 6000

Sub FormelAnpassen()

Dim spalte As Long

Dim zeile As Long

Dim bezugZeile As Long

zeile = 2

bezugZeile = 0

For spalte = 20 To 200

bezugZeile = bezugZeile + 1

Cells(zeile, spalte).FormulaArray = "=SUM(COUNTIF($A1:$F1,$L$" & bezugZeile & ":$Q$" & bezugZeile & "))"

End Sub

es wäre super wenn mir jemand helfen würde

danke im voraus

...zum Beitrag

Excel VBA activesheet statt tabellenname?

Hallo,

ich habe ein Makro aufgezeichnet und ich möchte dass die Formeln welche ich geschrieben habe immer von der aktiven Tabelle genommen werden.

Dadurch, dass ich das Makro aufgezeichnet habe, ist mir erst später aufgefallen, dass in den Formeln in den Zellen immer der Tabellenname steht: =WENNFEHLER(SVERWEIS(30;'42297033'!A5:T5;3;);"")

Ich möchte aber, dass diese Formeln immer die Tabelle nehmen, in der das Makro ausgeführt wurde.

Im Makro steht z.B. folgendes:

ActiveCell.FormulaR1C1 = _

"=IFERROR(VLOOKUP(30,'42297033'!R[3]C[-2]:R[3]C[17],3,),"""")"

'42297033' ist in dem Fall der Tabellennamen.

Noch zur Info was das gesamte Makro macht:

Ich erstelle eine neue Tabelle -> übertrage gewisse Daten von der alten Tabelle in die neue und das mit Formeln wie SVerweis() oder Wenn()

Kann ich Makro den Tabellenname durch etwas ersetzen, damit es richtig funktioniert?

Wäre schön wenn mir jemand helfen könnte, ich muss das spontan machen und bin ganz neu bei VBA

LG Benni

...zum Beitrag

Letze Zelle finden und Bis runter Ziehen VBA?

Excel letzte Zelle mit wert finden, eine Zelle darunter auswählen, formel einfügen und bis Zelle 500 runterziehen.

per VBA

...zum Beitrag

Excel VBA: Sverweis Formel - Makro einfügen!

Hi zusammen. Habe mir heute mein erstes Userform gebastelt (Google sei Dank...). Mein UF macht simpel nichts anderes, als Einträge aus der EIngabemaske in eine Tabelle zu füllen.

Dim emptyRow As Long

'Ermitteln der ersten leeren Zeile
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

'Wohin werden die eingegebenen Datein eingefüllt?
Cells(emptyRow, 1).Value = ComboBox1.Value
Cells(emptyRow, 5).Value = TextBox1.Value
Cells(emptyRow, 2).Value = TextBox2.Value
Cells(emptyRow, 3).Value = TextBox3.Value
Cells(emptyRow, 7).Value = TextBox4.Value

If OptionButton1.Value = True Then
    Cells(emptyRow, 6).Value = "1. Halbjahr"
End If

If OptionButton2.Value = True Then
    Cells(emptyRow, 6).Value = "2. Halbjahr"
End If


Me.Label7 = "Speichern erfolgreich!"

End Sub

Ich muss auch sagen, es funktioniert prächtig. Die Einträge werden alle korrekt erstellt etc. Nur habe ich in Spalte "H" meiner Tabelle, wo die Daten eingefügt werden, eine Spalte für SVerweis. Diese Formel soll jedesmal, wenn ein neuer Eintrag gespeichert wird, angefügt werden. Wenn ich von anfang an alle Formeln runterziehe bis ans Ende der Tabelle braucht Excel 2Minuten zum das Dokument zu öffnen... Formel lautet: =SVERWEIS($A2;'Tabelle1'!$A$1:$C$31;2;FALSCH) Wobei der Bezug A2 natürlich mit jeder Zeile anders ist (A2, A3, A4 etc.) Wenn ich die Formel "aufzeichne", erscheint folgendes, womit ich absolut nix anfangen kann.

Sub SVerweis_HC()
'
' SVerweis_HC Makro
'

'
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC1,'Tabelle1'!R[-1317]C[-7]:R[-1289]C[-6],2,FALSE)"
    Range("H1319").Select
End Sub

Kann hier wer helfen?

...zum Beitrag

Excel VBA eine große Menge leere Zeilen löschen?

Hallo, ich lade eine menge Datensätze in eine Tabelle rein und dabei werden auch die leeren Zeilen zwischendurch mal reinkopiert. Diese möchte ich nun entfernen. Das gelingt mir mittlerweile auch. Das Problem ist, dass es zu lange dauert bei ca. 2800 leere Zeilen, da diese einzeln angeschaut werden und gelöscht. Mein bisheriger Code:

Function loescheLeerenBereichn()

Dim Zelle As Long
Dim LetzteZeile As Long
LetzteZeile = Sheets("Lieferung").UsedRange.SpecialCells(xlCellTypeLastCell).Row
For Zelle = LetzteZeile To 7 Step -1 ' In Range(Cells(7, 4), Cells(LetzteZeile, 4))
If Cells(Zelle, 3) = "" Or Cells(Zelle, 3) = "." Then
Rows(Zelle).EntireRow.Delete Shift:=xlUp 
End If
Next Zelle

End Function

Wie kann man das schneller programmieren?

...zum Beitrag

Excel VBA - Formeln variabel nach unten kopieren (Makro)?

Hi Zusammen

Ich habe eine Tabelle, in der in Zellen A3:G3 Formeln sind. Ich will mittels Makro die Formeln nach unten kopieren. In Spalte I sind Datensätze. Wie kann ich ein Makro so erstellen, dass die Formeln in Spalten A:G nur soweit eingefügt werden, wie in Spalte I Daten vorhanden sind? Die Daten in Spalte I ist variabel (200, mal 300, etc.)

...zum Beitrag

Excel VBA Pfeiltasten überwachen?

Wie kann ich in Excel die Pfeiltasten überwachen lassen, d. h., immer wenn ich in der Tabelle "Tabelle1" die rechte Pfeiltaste drücke, soll nicht die Zelle rechts von der aktuellen Zelle ausgewählt werden (wie es ja normal wäre), sondern das Makro "Makro1" ausgeführt werden.

Geht das überhaupt?

...zum Beitrag

Gibt es eine Excel-Formel, um zu prüfen, ob ein Wert durchgestrichen ist?

Ich würde wissen, ob es eine Formel für Excel gibt, um zu prüfen, ob der Text in einer bestimmten Zelle durchgestrichen ist. Mit VBA wüsste ich schon wie das geht, allerdings konnte ich in dieser Tabelle bislang alles mit Excel-Formeln lösen und möchte jetzt nicht extra ein Makro aufmachen für diese kleine Aufgabe.

Danke im Voraus!

...zum Beitrag

Was möchtest Du wissen?

Deine Frage stellen