Jul 19, 2011

21045

Excel VBA - Werte aus zwei Zellen hintereinander in eine Zelle kopieren und eine Formatierung anwenden

Hallo,
stehe vor einem kleinen Problem. Habe zwei Spalten mit Zahlenwerten. Nun sollen sie in eine dritte Spalte kopiert werden, so dass sie hintereinander stehen (also z.B. Spalte 1: 4 Spalte 2: 7 Ergebnis in Spalte 3: 47). Allerdings sollen die Werte aus Spalte 2 hochgestellt eingefügt werden.

Hier nun meine Ansätze.

Als erstes versuchte ich es mittels SendKeys, bzw. Application SendKeys (hier noch ohne Schleife):

Sub Beispiel_1()
        Application.CutCopyMode = False
        ClearClipboard = True
    
       'Den Wert 2 hoch 3 in die Zelle A1 eintragen  
        
        Worksheets("Tabelle1").Select  
        
        Application.SendKeys ("2(%^4)")  
    
        ActiveSheet.Range("C1").Select  
        ActiveSheet.Paste Destination:=ActiveCell
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Bringt aber nichts, da Excel ja nur hoch 2 oder hoch 3 darstellen kann. Die Angaben bei Application.Sendkeys habe ich dann natürlich durch eine Variable ersetzt wo ich alles aus den Zellen befülle. Ich hab es dann aber nicht weiterverfolgt.

Ganz oben sieht man übrigens meine zwei Ansätze den Zwischenspeicher vorher zu leeren. Funktioniert aber nicht zuverlässig.

Dann habe ich mir eine Schleife zum Experimentieren gebastelt:

Sub Kopieren_Ziel_noch_zwei_Spalten()
Dim i As Long, ax(1 To 20, 1 To 1) As Variant, bx(1 To 20, 1 To 1) As Variant

For i = 1 To 20
ax(i, 1) = Int(Tabelle1.Range("A" & i).Value)
bx(i, 1) = Int(Tabelle1.Range("B" & i).Value)
Next i
With Worksheets("Tabelle1")
.Range(.Cells(1, 4), .Cells(20, 4)) = ax
.Range(.Cells(1, 5), .Cells(20, 5)) = bx
End With
End Sub

Ich habe danach krampfhaft versucht die beiden Variablen ax und bx im unteren Bereich zusammenzubringen und bx noch hochzustellen.
Selection.PasteSpecial Paste kann ich hier nicht nutzen, da ich ja nur den Inhalt der Zwischenablage auslese.

Wie komme ich nun am besten weiter? Vielleicht hat jemand einen Tipp für mich?

Danke schon mal im voraus!

Torsten

Please also mark the comments that contributed to the solution of the article

Content-Key: 170017

Url: https://administrator.de/contentid/170017

Printed on: April 24, 2024 at 09:04 o'clock

8 Comments

Latest comment

Hallo Torsten,

ich habe hier mal was mit meinen bescheidenen VBA-Kenntnissen gebastelt:

Sub CellConcatAsPower()
  Dim I As Long
  
  Worksheets("Tabelle1").Activate  
  
  With ActiveCell.CurrentRegion
    For I = 1 To .Rows.Count
      .Cells(I, 3).NumberFormat = "@"  
      .Cells(I, 3).HorizontalAlignment = xlRight
      .Cells(I, 3) = .Cells(I, 1) & .Cells(I, 2)
      .Cells(I, 3).Characters(Len(.Cells(I, 1)) + 1, Len(.Cells(I, 2))).Font.Superscript = True
    Next I
  End With
End Sub

Ich gehe davon aus, dass deine Tabelle ringsum von leeren Zellen umgeben ist. Du musst eine Zelle der Tabelle markieren und dann das Makro ausführen.

Die Tabelle kann an beliebigen Koordinaten auf dem Arbeitsblatt liegen.

Gruß
Friemler

Hallo Torsten!

Versuchs mal so:

Option Explicit

Const StartZeile = 2

Sub FillColumnC_Value()
    Dim Cell As Range, Len_A As Long, Len_B As Long
    
    With Columns("C:C")  
        .NumberFormat = "@"                                             'Text-Format in Spalte C  
        .HorizontalAlignment = xlRight                                  'Ausrichtung Rechts  
    End With
    
    For Each Cell In Range("A:A")  
        If Cell.Row >= StartZeile Then                                  'Ab Startzeile  
            If IsEmpty(Cell) Then Exit Sub                              'Bis zur 1. Leerzeile  
                
            Len_A = Len(Cell.Text)                                      'Anzahl Charakter Spalte A  
            Len_B = Len(Cell.Offset(0, 1).Text)                         'Anzahl Charakter Spalte B  
            
            With Cell.Offset(0, 2)
                .Value = Cell.Text & Cell.Offset(0, 1).Text             'Spalte C = Spalte A & Spalte B  
                .Characters(Len_A + 1, Len_B).Font.Superscript = True   'Wert aus Spalte B Hochgestellt  
            End With
        End If
    Next
End Sub

Gruß Dieter

@Friemler
Spalte C soll doch die Schreibform 4^7 haben, wobei nur die 7 Hochgestellt sein soll

PS. Die Codezeile 18 kann auch entfernt werden und in Codezeile 22 anstelle von Len_B einfach eine -1 stehen. Bzw. etwas kürzer:

Option Explicit

Const StartZeile = 2

Sub FillColumnC_Value()
    Dim Cell As Range
    
    With Columns("C:C")  
        .NumberFormat = "@"                                                     'Text-Format in Spalte C  
        .HorizontalAlignment = xlRight                                          'Ausrichtung Rechts  
    End With
    
    For Each Cell In Range("A:A")  
        If Cell.Row >= StartZeile Then                                          'Ab Startzeile  
            If IsEmpty(Cell) Then Exit Sub                                      'Bis zur 1. Leerzeile  
                
            With Cell.Offset(0, 2)
                .Value = Cell.Text & Cell.Offset(0, 1).Text                     'Spalte C = Spalte A & Spalte B  
                .Characters(Len(Cell.Text) + 1, -1).Font.Superscript = True     'Wert aus Spalte B Hochgestellt  
            End With
        End If
    Next
End Sub

Hallo Dieter,

danke für den Hinweis (und die Vorlage

). Ist bereits korrigiert.

Gruß
Friemler

Hallo Friemler und Dieter,
vielen Dank für das schnelle Feedback !!!

Da ich jetzt erst Zugriff auf einen PC habe konnte ich leider nicht schneller reagieren. Testen kann ich es aber nachher erst, wenn ich bei mir zuhause bin.

@Friemler
Wegen der Formatierung (Hochstellung) hätte ich ohnehin noch gefragt, da sich bei deinem Beispiel ja die Formatierung auf die ganze Zelle bezieht.
Ansonsten müsste es ja so klappen.

@Dieter
Boa, das muss ich mir nachher erst mal in Ruhe auf der Zunge zergehen lassen... Ich werde es erstmal testen und es dann in Ruhe Schritt für Schritt durcharbeiten. Schließlich geht es ja nicht um eine fertige Lösung; viel wichtiger ist natürlich das Verstehen.

Also, wie gesagt erstmal vielen Dank, ich melde mich auf jeden Fall dann heute noch !!!

Bis dann!
Torsten

Hallo,
also das ist ja wirklich der Hammer! Danke Dieter !!!

Funktioniert wirklich perfekt, genau so wie ich`s gedacht habe.
Ich konnte auch deinen Code nachvollziehen, hätte ihn aber beweiten nicht so schreiben können. Naja, man lernt ja immer dazu.

Ich hab "Const StartZeile = 2 " nur auf 1 gesetzt, da ich in diesem Beispiel ja eh keine Kopzeile o.Ä. hatte.

Als besonderes Schmakerl hat du ja auch gleich "If IsEmpty(Cell) Then Exit Sub" eingefügt, so dass ich nicht feste Werte vorgeben muss. Dies wäre ja sonst bei unterschiedlichen Datenmengen ziemlich unflexibel.

Übrigens sehe ich das sicher richtig: du musst bei der Zielzelle erst einmal einen definierten Punkt finden, von dem du ausgehen musst (.HorizontalAlignment = xlRight), damit du in Schritten von dieser Position (also "0") nach rechts und links gehen kannst und beide Bereiche unterschiedlich formatieren kannst z.B. mit "Font.Superscript" für das Hochstellen(.Characters(Len(Cell.Text) + 1, -1).

Was bedeutet eigentlich genau dieses "Cell.Offset" ?

Also, tausend Dank nochmal !!!

@Friemler
Tausend Dank natürlich auch an dich; mich würde auch mal interessieren wie du deine Lösung angepasst hat. Man kann ja nur lernen...

Einen schönen Abend wünsche ich euch Beiden noch !!!

Gruß
Torsten

Hallo Torsten,

ich musste die Zeilen

.Cells(I, 3).NumberFormat = "@"
.Cells(I, 3).HorizontalAlignment = xlRight

ergänzen (Zeile 8 und 9) und Zeile 11 um

Characters(Len(.Cells(I, 1)) + 1, Len(.Cells(I, 2))).

ergänzen.

Das HorizontalAlignment hat nichts mit dem Suchen eines Startpunktes zu tun. Das ist die Ausrichtung des Zelleninhaltes, hier also rechtsbündig, wie das bei Zahlen gewöhnlich so ist. Durch die vorherige Zeile mit NumberFormat wird die Formatierung der Zelle auf Text gestellt und Textzellen sind normalerweise linksbündig formatiert. Die Zelle muss als Text formatiert sein, damit das hochstellen funktioniert.

Ich nutze Excel 2003. Die Sub von Dieter sieht mir wie ein Eventhandler aus (wegen XXX_Value). Mein Excel erzeugt jedoch keinen Event, in der Hilfe habe ich so einen Event auch nicht gefunden. Ich muss das Makro also auch manuell ausführen. Wie sieht das bei Dir aus? Welche Excelversion?

Gruß
Friemler

Hallo Friemler,
ja, ich hatte mich auch falsch ausgedrückt. Ist schon klar, dass damit die Zellenausrichtung gemeint ist.
Ich wollte damit nur sagen, wenn ich die Zelle damit ausgerichtet habe, aber noch keine Werte reingeschrieben habe, befinde ich mich in dieser Zelle am Punkt 0. Von da aus kann ich z.B. mittels +1 oder -1 nach jeder weisen und darauf weitere Formatierungen anwenden.

Ich nutze das Makro, da es ja nur der Vortest ist, momentan auch nur manuell. Wenn ich es dann für die Produktivdateien anpasse müsste es schon automatisch ablaufen (mittels auto_open).

Ich nutze übrigens Excel 2007, obwohl mir Excel 2003 ehrlich gesagt mehr zugesagt hat. Naja, was solls...

Deine Variante werde ich dann morgen testen (verdammt, ist ja schon morgen...). Ich muss mich jetzt aber mal ein bissl auf`s Ohr hauen.

Ich melde mich dann.

Gruß
Torsten

Hallo Torsten und Friemler!

@Friemler
Also, bei der Sub handelt es sich um keinen Event. Der Unterstrich ist nur drinnen, weil 'FillColumnCValue' etwas komisch aussieht.

@Torsten
Im Groben hat Friemler ja schon das meiste erklärt

Die Positionsangaben beziehen sich nicht auf die Zelle, sondern nur auf den Text-Inhalt in der Zelle.
In der Zell-Formatierung wird lediglich das Zahlenformat Text und die Ausrichtung Rechts beeinflusst .
Ansonsten kannst Du z.B. die Schrift-Art, -Größe, -Farbe... nach Deinen Wünschen anpassen.

Der Offset ist eine relative Positionsangabe zu der jeweiligen Zelle z.B.
Set Zelle = Range("B2")
dann beziehen sich die Offsets(Zeile, Spalte):
Zelle.Offset(0 ,+0) = Range("B2")
Zelle.Offset(0 ,-1) = Range("A2")
Zelle.Offset(0 ,+1) = Range("C2")
Zelle.Offset(0 ,+2) = Range("D2")

Zelle.Offset(+0 ,0) = Range("B2")
Zelle.Offset(-1 ,0) = Range("B1")
Zelle.Offset(+1 ,0) = Range("B3")
Zelle.Offset(+2 ,0) = Range("B4")

Die Zeilenhöhe solltest Du auf mindestens 15 Punkten setzen, weil sich sonst je nach Inhalt in Spalte C (Text/Leer) die Zeilenhöhe verändert.

Und damit das Ganze automatisch (per Event) funktioniert, kopierst Du diesen Code in das entsprechende Tabellenblatt:

Const StartZeile = 1

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Or Target.Row < StartZeile Then Exit Sub
    
    If Not Intersect(Target, Range("A:B")) Is Nothing Then  
        If Not IsEmpty(Cells(Target.Row, "A")) And Not IsEmpty(Cells(Target.Row, "B")) Then  
            With Cells(Target.Row, "C")  
                .NumberFormat = "@"  
                .HorizontalAlignment = xlRight
                .Value = .Offset(0, -2).Text & .Offset(0, -1).Text
                .Characters(Len(.Offset(0, -2).Text) + 1, -1).Font.Superscript = True
            End With
        Else
            Cells(Target.Row, "C") = ""  
        End If
    End If
End Sub

Damit werden Änderungen in der Spalte A und B ab der StartZeile automatisiert erkannt. D.h. wenn in Spalte A und B ein Wert steht, dann wird in Spalte C ein Wert angezeigt, ansonsten ist die Spalte C Leer.

Gruß Dieter

German solved Question VB for Applications Development

Hotly discussed

Check of ZFW Firewallgleixnerd - 5 Comments

Wireguard VPN on UDM Pro behind Fritzbox - Handshake did not completejstricker - 3 Comments

How to set up and configure a Linux GRE tunnelAlexWisha - 3 Comments