Herbers Excel/VBA-Archiv

VBA Concatenate einfügen über gesamte Spalte

• VBA Concatenate einfügen über gesamte Spalte von Monika vom 30.10.2014 12:10:53
• AW: VBA Concatenate einfügen über gesamte Spalte - von fcs am 30.10.2014 12:33:38
  • AW: VBA Concatenate einfügen über gesamte Spalte - von Monika am 30.10.2014 12:48:29
• Lösung per .FormulaR1C1 - von NoNet am 30.10.2014 12:44:25
  • AW: Lösung per .FormulaR1C1 - von Monika am 30.10.2014 15:03:20

Betrifft: VBA Concatenate einfügen über gesamte Spalte von: Monika
Geschrieben am: 30.10.2014 12:10:53

Hallo zusammen,

ich hänge hier leider mal wieder ein bisschen und hoffe auf eine Erleuchtung :)
Ich habe zwei Spalten (D und E) deren Inhalt ich in Spalte F verketten will (allerdings erst ab Spalte 2, das konnte ich leider noch nicht umsetzen).Ich hab grob schon mal etwas erstellt, mein Problem ist, dass ich zwar die Formel einfügen kann, aber nicht verstehe, wie ich jetzt sage, dass nicht immer der gleiche Wert eingefügt werden soll, sondern immer eine Spalte runtergesprungen werden soll. Also im Prinzip, wie wenn man in Excel die Formel runter zieht und kein $-Zeichen eingebaut hat... Ich hoffe, das ist verständlich.

Hier ist, was ich bisher habe:

Sub CreatePrimaryKey()

Do While ActiveCell.Offset(0, -1) <> ""  'Loops until the active cell is blank.

  ActiveCell.Offset(0, 1).FormulaR1C1 = _
     ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0)

  ActiveCell.Offset(1, 0).Select
  ActiveCell.Formula = "=CONCATENATE(D2,E2)"
  
Loop

End Sub

In der Concatenate Funktion stehen jetzt noch die exakten Zellen - könnt ihr mir vielleicht sagen, wie ich das mache, dass nicht immer D2 und E2 verkettet werden sondern eben immer eine Spalte runter gewandert wird?

Vielen Dank schon mal und liebe Grüße :)

Betrifft: AW: VBA Concatenate einfügen über gesamte Spalte von: fcs
Geschrieben am: 30.10.2014 12:33:38

Hallo Monika,

in der R1C1-Schreibweise kann man den Bezug in den Formeln "einfach" auf relativ einstellen.

Sub CreatePrimaryKey()
Range("F2").Select
Do While ActiveCell.Offset(0, -1) <> ""  'Loops until the active cell is blank.

  ActiveCell.FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
  ActiveCell.Offset(1, 0).Select
  
Loop

End Sub

Allerdings sollte man hier nicht unbedingt mit Select und Activecell in einer Schleife arbeiten, sondern die die letzte Zeile mit Daten ermitteln und die Formeln in einer Anweisung einfügen.

'Alternative - alle Formeln werden in Spalte F mit einer Anweisung eingefügt
Sub CreatePrimaryKey_2()
  Dim Zeile As Long
  With ActiveSheet
    'letzte Zeile in Spalte E
    Zeile = .Cells(.Rows.Count, 5).End(xlUp).Row
    If Zeile >= 2 Then
      With .Range(.Cells(2, 6), .Cells(Zeile, 6))
        .FormulaR1C1 = "=CONCATENATE(RC[-2],RC[-1])"
        .Calculate
'        .Value=.Value 'Formeln durch Werte ersetzen
      End With
    End If
  End With
End Sub

Gruß
Franz

Betrifft: AW: VBA Concatenate einfügen über gesamte Spalte von: Monika
Geschrieben am: 30.10.2014 12:48:29

Hallo,

vielen Dank, die zweite Version funktioniert super! :)

Betrifft: Lösung per .FormulaR1C1 von: NoNet
Geschrieben am: 30.10.2014 12:44:25

Hallo Monika,

zuerst einmal eine kleine Korrektur Deiner Formulierung : "Spalte 2" und "eine Spalte runter" gibt es in Excel nicht wirklich, denn die SPALTEN werden standardmäßig mit Buchstaben (A bis XFD) bezeichnet, varrieren also in der Horizontalen ("von links nach rechts" und NICHT "oben nach unten"), während die ZEILEN von 1 bis 1048576 nummeriert sind, also in der Vertikalen "von oben nach unten" variieren !

Hier eine kurze "schmerzlose" VBA-Lösung Deines Anliegens :

Sub CreatePrimaryKey2()
    With [F2]
        .Resize(.End(xlDown).Row - .Row).FormulaR1C1 = _
            "=CONCATENATE(RC[-1],RC[-2])"
    End With
End Sub

Salut, NoNet

Betrifft: AW: Lösung per .FormulaR1C1 von: Monika
Geschrieben am: 30.10.2014 15:03:20

Oh tut mir Leid, ich meinte natürlich Zeile runter, das hast Du richtig erkannt!
Danke noch für deinen Tip :)

Beiträge aus den Excel-Beispielen zum Thema "VBA Concatenate einfügen über gesamte Spalte"