Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1172to1176
1172to1176
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VERKETTEN über VBA

Forumthread: VERKETTEN über VBA

VERKETTEN über VBA
Dirk
Guten Morgen,
hab da ein Problem die folgende Formel in VBA umzusetzen.
Tabelle1

 ABCDE
1Nr. 1Nr. 2Nr. 3Nr. 4Ergebnis
201.01.00.0001.01.00.00
301.02.01.0001.02.01.00
402.02.01.0102.02.01.01

Formeln der Tabelle
ZelleFormel
E2=VERKETTEN(TEXT(A2;"00"); TEXT(B2;"00"); TEXT(C2;"00"); TEXT(D2;"00"))*1
E3=VERKETTEN(TEXT(A3;"00"); TEXT(B3;"00"); TEXT(C3;"00"); TEXT(D3;"00"))*1
E4=VERKETTEN(TEXT(A4;"00"); TEXT(B4;"00"); TEXT(C4;"00"); TEXT(D4;"00"))*1


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Die Formel (das Makro) soll nur ausgeführt werden, wenn die Eingabe in der entsprechenden Zeile (beschränkt auf Spalte A bis D) erfolgt ist.
Die Werte in Spalte A bis D sind mit folgendem Zahlenformat A 00, B-D "."00 hinterlegt. Das Zahlenformat für Spalte E sieht wie folgt aus: 00"."00"."00"."00.
Kann mir da am frühen Morgen schon jemand helfen?
Danke
Gruß Dirk
Anzeige

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Benutzer
Anzeige
VERKETTEN über VBA
18.08.2010 09:51:46
Erich
Hi Dirk,
statt VERKETTEN kann man auch einfach & zwischen die Teile setzen.
Die Formate in den Spalten A:D sind beliebig, die Werte dort müssen Zahlen sein.
Hier zunächst zwei kürzere Formelvarianten (Spalten F und G, die Formate sind wie bei dir):
 ABCDEFG
1Nr. 1Nr. 2Nr. 3Nr. 4VBAErgebnisoder
2110001.01.00.0001.01.00.0001.01.00.00
3121001.02.01.0001.02.01.0001.02.01.00
4221102.02.01.0102.02.01.0102.02.01.01
533 3 03.03.00.0303.03.00.03

Formeln der Tabelle
ZelleFormel
F2=1*(TEXT(A2;"00")&TEXT(B2;"00")&TEXT(C2;"00")&TEXT(D2;"00"))
G2=100*(100*(100*A2+B2)+C2)+D2

Spalte E wurde mit VBA gefüllt. E5 ist leer, weil A5:D5 nicht vollständig eingegeben ist.
Hier der Code: Option Explicit Sub myVerkette() Dim zz As Long For zz = 2 To Cells(Rows.Count, 1).End(xlUp).Row If Application.Count(Cells(zz, 1).Resize(, 4)) = 4 Then Cells(zz, 5) = 100 * (100 * (100 * Cells(zz, 1) + _ Cells(zz, 2)) + Cells(zz, 3)) + Cells(zz, 4) Else Cells(zz, 5).ClearContents End If Next zz End Sub Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
AW: VERKETTEN über VBA
18.08.2010 10:47:41
Dirk
Hallo Erich,
wow, das ging ja schnell und dann auch noch mit zusätzlichen Formel-Möglichleiten, vielen Dank!
Eine Frage hab ich da noch :-)
Kann man das Makro immer dann ausführen (automatisch), wenn in den Zeilen die Werte eingetragen werden?
Danke für Deine Zeit
Gruß Dirk
Anzeige
Verketten bei Change-Ereignis
18.08.2010 11:17:49
Erich
Hi Dirk,
die Prozedur "myVerkette" kannst löschen und stattdessen in den Code der Tabelle
diese Ereignisprozedur kopieren: 

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngB As Range, zz As Long
Set rngB = Intersect(Target, Cells(2, 1).Resize(Rows.Count - 1, 4))
If rngB Is Nothing Then Exit Sub
For zz = Application.Max(2, rngB.Row) To rngB.Row + rngB.Rows.Count - 1
If Application.Count(Cells(zz, 1).Resize(, 4)) = 4 Then
Cells(zz, 5) = 100 * (100 * (100 * Cells(zz, 1) + _
Cells(zz, 2)) + Cells(zz, 3)) + Cells(zz, 4)
Else
Cells(zz, 5).ClearContents
End If
Next zz
End Sub
Das Format der Spalte E brauchst du ja nur einmal per Hand einzurichten.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
Verketten mit Format?
18.08.2010 11:57:53
Dirk
Hallo Erich,
Deine Lösung klappt super, vielen Dank.
Würde gerne auch das Format mit in den CODE einbeziehen.
Habe es mit Cells(zz, 5).NumberFormat = "00"."00"."00"."00" versucht, doch da meckert er nur Syntaxfehler. Darf ich dich noch mal um Hilfe bitten?
Vielen Dank
Gruß Dirk
Anzeige
Format?
18.08.2010 12:49:51
Peter.H
Hallo Dirk
so sollte es funktionieren:
Cells(zz, 5).NumberFormat = "00"".""00"".""00"".""00"
Gruß
Peter
"" machen den Unterschied...
18.08.2010 12:53:00
Ramses
Hallo
Cells(zz, 5).NumberFormat = "00"".""00"".""00"".""00"
Zur Info:
Einfach mal den Makrorekorder starten, dein Aktion durchführen und stoppen.
Anschliessend den Code ansehen wenn du nicht mehr weiterkommst.
Gruss Rainer
Anzeige
Verketten und Format
18.08.2010 12:55:20
Erich
Hi Dirk,
das Zahlenformat jeweils für einzelne Zellen festzulegen, bläht die Dateigröße unnötig auf.
Was spricht dagegen, der Spalte E das Format einmalig per Hand zu verpassen?
Mit Cells(zz, 5).ClearContents bleibt das Format erhalten.
Wenn schon per Code, würde ich auch in VBA der ganzen Spalte das Format geben.
Oder sollen in Spalte E noch Zahlen in Zellen mit anderen Formaten stehen?
Per Makroaufzeichnung kannst du leicht herausbekommen, wie die richtige Syntax ist: 

Sub Makro1()
' Makro1 Makro
' Makro am 18.08.2010 von Erich aufgezeichnet
Selection.NumberFormat = "00"".""00"".""00"".""00"
End Sub

Obwohl ich das für unnötig und eher schädlich halte, hier der Code,
der bei jedem Ausfüllen in Spalte E die Spalte formatiert: 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngB As Range, zz As Long, bolAend As Boolean
Set rngB = Intersect(Target, Cells(2, 1).Resize(Rows.Count - 1, 4))
If rngB Is Nothing Then Exit Sub
For zz = Application.Max(2, rngB.Row) To rngB.Row + rngB.Rows.Count - 1
If Application.Count(Cells(zz, 1).Resize(, 4)) = 4 Then
Cells(zz, 5) = 100 * (100 * (100 * Cells(zz, 1) + _
Cells(zz, 2)) + Cells(zz, 3)) + Cells(zz, 4)
bolAend = True
Else
Cells(zz, 5).ClearContents
End If
Next zz
If bolAend Then Columns(5).NumberFormat = "00"".""00"".""00"".""00"
End Sub
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort
Anzeige
Vielen Dank an alle ...
18.08.2010 13:16:43
Dirk
Hallo ...
@Erich
Deine Einwände bzgl. Dateigröße werde ich im Auge behalten … und … ja, du liegst mit deiner Vermutung richtig, nicht die ganze Spalte E soll mit diesem Zellformat bestückt werden.
Vielen Dank für Deine Unterstützung!
@Peter & Rainer
Auch Euch vielen Dank!
Der Hinweis mit dem Makrorekorder ist gut, nur vergess ich ihn leider viel zu oft, sorry
Habe mich vorerst für die Varinate Cells(zz, 5).NumberFormat = "00"".""00"".""00"".""00" entschieden.
Danke und Gruß
Dirk
Anzeige
;
Anzeige

Infobox / Tutorial

VBA zum Verketten von Text in Excel

Schritt-für-Schritt-Anleitung

Um die Funktionalität "Verketten" in Excel über VBA umzusetzen, kannst Du die folgende Schritt-für-Schritt-Anleitung nutzen:

  1. Öffne die Excel-Datei und gehe zu dem Arbeitsblatt, in dem Du die Verkettung durchführen möchtest.
  2. Öffne den VBA-Editor mit ALT + F11.
  3. Füge ein neues Modul hinzu:
    • Rechtsklicke im Projekt-Explorer auf das Projekt und wähle Einfügen > Modul.

  4. Füge den folgenden VBA-Code ein:

    Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
   Dim rngB As Range, zz As Long
   Set rngB = Intersect(Target, Cells(2, 1).Resize(Rows.Count - 1, 4))
   If rngB Is Nothing Then Exit Sub
   For zz = Application.Max(2, rngB.Row) To rngB.Row + rngB.Rows.Count - 1
       If Application.Count(Cells(zz, 1).Resize(, 4)) = 4 Then
           Cells(zz, 5) = 100 * (100 * (100 * Cells(zz, 1) + _
           Cells(zz, 2)) + Cells(zz, 3)) + Cells(zz, 4)
       Else
           Cells(zz, 5).ClearContents
       End If
   Next zz
End Sub
  5. Schließe den VBA-Editor und gehe zurück zu Deinem Arbeitsblatt.
  6. Teste die Funktion, indem Du Werte in die Spalten A bis D eingibst. Spalte E sollte automatisch aktualisiert werden.

Häufige Fehler und Lösungen

  • Fehler: "Typenkonflikt": Stelle sicher, dass die Zellen in den Spalten A bis D als Zahlen formatiert sind.
  • Fehler: "Makro wird nicht ausgeführt": Überprüfe, ob das Makro aktiviert ist und die Datei im richtigen Format (z.B. .xlsm) gespeichert wurde.
  • Fehler: "Syntaxfehler": Achte darauf, dass die Anführungszeichen im Format korrekt gesetzt sind, z.B. Cells(zz, 5).NumberFormat = "00"".""00"".""00"".""00".

Alternative Methoden

Falls Du die Funktion "ver-kett-en" ohne VBA anwenden möchtest, kannst Du die Formel mit dem &-Operator verwenden. Zum Beispiel:

=TEXT(A2;"00") & TEXT(B2;"00") & TEXT(C2;"00") & TEXT(D2;"00")

Dadurch erhältst Du das gleiche Ergebnis, ohne ein Makro zu verwenden. Die Verwendung von TEXT sorgt dafür, dass die Zahlen im gewünschten Format angezeigt werden.

Praktische Beispiele

Hier sind einige praktische Beispiele zur Anwendung von VBA zum Verketten von Text:

  1. Verketten von Datumsangaben:

    • Wenn Du in den Zellen A2 bis D2 Datumswerte hast, könntest Du sie in E2 so verketten, dass sie ein spezifisches Format erhalten.

  2. Automatisches Aktualisieren:

    • Mit der oben beschriebenen Worksheet_Change-Prozedur wird die Verkettung automatisch ausgeführt, wenn Werte in den Zellen A bis D eingegeben werden.

Tipps für Profis

  • Nutze den Makrorekorder: Wenn Du dir unsicher über die Syntax bist, starte den Makrorekorder und führe die Aktion manuell aus. Der Rekorder generiert den entsprechenden VBA-Code.

  • Optimierung der Performance: Wenn Du mit großen Datenmengen arbeitest, solltest Du Application.ScreenUpdating und Application.Calculation temporär deaktivieren, um die Ausführungsgeschwindigkeit zu erhöhen:

    Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
' Dein Code hier
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

FAQ: Häufige Fragen

1. Wie kann ich das Format in Spalte E über VBA festlegen?
Du kannst das Format mit dem folgenden Code festlegen:

Cells(zz, 5).NumberFormat = "00"".""00"".""00"".""00"

2. Funktioniert das auch in älteren Excel-Versionen?
Ja, die meisten der beschriebenen VBA-Funktionen sind in Excel 2007 und neueren Versionen verfügbar. Achte jedoch darauf, dass einige Funktionen in älteren Versionen abweichen können.

Beliebteste Forumthreads (12 Monate)

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