Top-Themen

AppleEntwicklungHardwareInternetLinuxMicrosoftMultimediaNetzwerkeOff TopicSicherheitSonstige SystemeVirtualisierungWeiterbildungZusammenarbeit

Aktuelle Themen

Administrator.de FeedbackApache ServerAppleAssemblerAudioAusbildungAuslandBackupBasicBatch & ShellBenchmarksBibliotheken & ToolkitsBlogsCloud-DiensteClusterCMSCPU, RAM, MainboardsCSSC und C++DatenbankenDatenschutzDebianDigitiales FernsehenDNSDrucker und ScannerDSL, VDSLE-BooksE-BusinessE-MailEntwicklungErkennung und -AbwehrExchange ServerFestplatten, SSD, RaidFirewallFlatratesGoogle AndroidGrafikGrafikkarten & MonitoreGroupwareHardwareHosting & HousingHTMLHumor (lol)Hyper-VIconsIDE & EditorenInformationsdiensteInstallationInstant MessagingInternetInternet DomäneniOSISDN & AnaloganschlüsseiTunesJavaJavaScriptKiXtartKVMLAN, WAN, WirelessLinuxLinux DesktopLinux NetzwerkLinux ToolsLinux UserverwaltungLizenzierungMac OS XMicrosoftMicrosoft OfficeMikroTik RouterOSMonitoringMultimediaMultimedia & ZubehörNetzwerkeNetzwerkgrundlagenNetzwerkmanagementNetzwerkprotokolleNotebook & ZubehörNovell NetwareOff TopicOpenOffice, LibreOfficeOutlook & MailPapierkorbPascal und DelphiPeripheriegerätePerlPHPPythonRechtliche FragenRedHat, CentOS, FedoraRouter & RoutingSambaSAN, NAS, DASSchriftartenSchulung & TrainingSEOServerServer-HardwareSicherheitSicherheits-ToolsSicherheitsgrundlagenSolarisSonstige SystemeSoziale NetzwerkeSpeicherkartenStudentenjobs & PraktikumSuche ProjektpartnerSuseSwitche und HubsTipps & TricksTK-Netze & GeräteUbuntuUMTS, EDGE & GPRSUtilitiesVB for ApplicationsVerschlüsselung & ZertifikateVideo & StreamingViren und TrojanerVirtualisierungVisual StudioVmwareVoice over IPWebbrowserWebentwicklungWeiterbildungWindows 7Windows 8Windows 10Windows InstallationWindows MobileWindows NetzwerkWindows ServerWindows SystemdateienWindows ToolsWindows UpdateWindows UserverwaltungWindows VistaWindows XPXenserverXMLZusammenarbeit
GELÖST

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

Frage Entwicklung VB for Applications

Mitglied: goodbytes

goodbytes (Level 2) - Jetzt verbinden

19.07.2011 um 14:53 Uhr, 13126 Aufrufe, 8 Kommentare

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):

01.
Sub Beispiel_1() 
02.
        Application.CutCopyMode = False 
03.
        ClearClipboard = True 
04.
     
05.
       'Den Wert 2 hoch 3 in die Zelle A1 eintragen 
06.
         
07.
        Worksheets("Tabelle1").Select 
08.
         
09.
        Application.SendKeys ("2(%^4)") 
10.
     
11.
        ActiveSheet.Range("C1").Select 
12.
        ActiveSheet.Paste Destination:=ActiveCell 
13.
        Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 
14.
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:

<code>
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
<code>

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
Mitglied: Friemler
19.07.2011 um 16:16 Uhr
Hallo Torsten,

ich habe hier mal was mit meinen bescheidenen VBA-Kenntnissen gebastelt:
01.
Sub CellConcatAsPower() 
02.
  Dim I As Long 
03.
   
04.
  Worksheets("Tabelle1").Activate 
05.
   
06.
  With ActiveCell.CurrentRegion 
07.
    For I = 1 To .Rows.Count 
08.
      .Cells(I, 3).NumberFormat = "@" 
09.
      .Cells(I, 3).HorizontalAlignment = xlRight 
10.
      .Cells(I, 3) = .Cells(I, 1) & .Cells(I, 2) 
11.
      .Cells(I, 3).Characters(Len(.Cells(I, 1)) + 1, Len(.Cells(I, 2))).Font.Superscript = True 
12.
    Next I 
13.
  End With 
14.
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
Bitte warten ..
Mitglied: 76109
19.07.2011 um 17:30 Uhr
Hallo Torsten!

Versuchs mal so:
01.
Option Explicit 
02.
 
03.
Const StartZeile = 2 
04.
 
05.
Sub FillColumnC_Value() 
06.
    Dim Cell As Range, Len_A As Long, Len_B As Long 
07.
     
08.
    With Columns("C:C") 
09.
        .NumberFormat = "@"                                             'Text-Format in Spalte C 
10.
        .HorizontalAlignment = xlRight                                  'Ausrichtung Rechts 
11.
    End With 
12.
     
13.
    For Each Cell In Range("A:A") 
14.
        If Cell.Row >= StartZeile Then                                  'Ab Startzeile 
15.
            If IsEmpty(Cell) Then Exit Sub                              'Bis zur 1. Leerzeile 
16.
                 
17.
            Len_A = Len(Cell.Text)                                      'Anzahl Charakter Spalte A 
18.
            Len_B = Len(Cell.Offset(0, 1).Text)                         'Anzahl Charakter Spalte B 
19.
             
20.
            With Cell.Offset(0, 2) 
21.
                .Value = Cell.Text & Cell.Offset(0, 1).Text             'Spalte C = Spalte A & Spalte B 
22.
                .Characters(Len_A + 1, Len_B).Font.Superscript = True   'Wert aus Spalte B Hochgestellt 
23.
            End With 
24.
        End If 
25.
    Next 
26.
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:
01.
Option Explicit 
02.
 
03.
Const StartZeile = 2 
04.
 
05.
Sub FillColumnC_Value() 
06.
    Dim Cell As Range 
07.
     
08.
    With Columns("C:C") 
09.
        .NumberFormat = "@"                                                     'Text-Format in Spalte C 
10.
        .HorizontalAlignment = xlRight                                          'Ausrichtung Rechts 
11.
    End With 
12.
     
13.
    For Each Cell In Range("A:A") 
14.
        If Cell.Row >= StartZeile Then                                          'Ab Startzeile 
15.
            If IsEmpty(Cell) Then Exit Sub                                      'Bis zur 1. Leerzeile 
16.
                 
17.
            With Cell.Offset(0, 2) 
18.
                .Value = Cell.Text & Cell.Offset(0, 1).Text                     'Spalte C = Spalte A & Spalte B 
19.
                .Characters(Len(Cell.Text) + 1, -1).Font.Superscript = True     'Wert aus Spalte B Hochgestellt 
20.
            End With 
21.
        End If 
22.
    Next 
23.
End Sub
Bitte warten ..
Mitglied: Friemler
19.07.2011 um 18:12 Uhr
Hallo Dieter,

danke für den Hinweis (und die Vorlage ). Ist bereits korrigiert.

Gruß
Friemler
Bitte warten ..
Mitglied: goodbytes
19.07.2011 um 18:30 Uhr
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
Bitte warten ..
Mitglied: goodbytes
19.07.2011 um 23:10 Uhr
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
Bitte warten ..
Mitglied: Friemler
19.07.2011 um 23:23 Uhr
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
Bitte warten ..
Mitglied: goodbytes
20.07.2011 um 01:04 Uhr
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
Bitte warten ..
Mitglied: 76109
20.07.2011 um 07:38 Uhr
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:
01.
Const StartZeile = 1 
02.
 
03.
Private Sub Worksheet_Change(ByVal Target As Range) 
04.
    If Target.Count > 1 Or Target.Row < StartZeile Then Exit Sub 
05.
     
06.
    If Not Intersect(Target, Range("A:B")) Is Nothing Then 
07.
        If Not IsEmpty(Cells(Target.Row, "A")) And Not IsEmpty(Cells(Target.Row, "B")) Then 
08.
            With Cells(Target.Row, "C") 
09.
                .NumberFormat = "@" 
10.
                .HorizontalAlignment = xlRight 
11.
                .Value = .Offset(0, -2).Text & .Offset(0, -1).Text 
12.
                .Characters(Len(.Offset(0, -2).Text) + 1, -1).Font.Superscript = True 
13.
            End With 
14.
        Else 
15.
            Cells(Target.Row, "C") = "" 
16.
        End If 
17.
    End If 
18.
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
Bitte warten ..
Neuester Wissensbeitrag
Windows 10

Powershell 5 BSOD

(8)

Tipp von agowa338 zum Thema Windows 10 ...

Ähnliche Inhalte
VB for Applications
gelöst Excel VBA Werte von 2 verschiedenen Sheets vergleichen und aktualisieren (4)

Frage von drimrim zum Thema VB for Applications ...

VB for Applications
gelöst Excel VBA Eine oder mehrere Zellen Verschieben (2)

Frage von batchnewbie zum Thema VB for Applications ...

Microsoft Office
gelöst Excel VBA Letzte Zelle Suchen dann rechnen und Wert in Zelle Übertragen (3)

Frage von Addi089 zum Thema Microsoft Office ...

VB for Applications
Excel VBA Sortierung von Daten (5)

Frage von easy4breezy zum Thema VB for Applications ...

Heiß diskutierte Inhalte
Microsoft
Ordner mit LW-Buchstaben versehen und benennen (21)

Frage von Xaero1982 zum Thema Microsoft ...

Netzwerkmanagement
gelöst Anregungen, kleiner Betrieb, IT-Umgebung (18)

Frage von Unwichtig zum Thema Netzwerkmanagement ...

Windows Update
Treiberinstallation durch Windows Update läßt sich nicht verhindern (17)

Frage von liquidbase zum Thema Windows Update ...