Top-Themen

Aktuelle Themen (A bis Z)

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

Mitglied: goodbytes

goodbytes (Level 2) - Jetzt verbinden

19.07.2011 um 14:53 Uhr, 15076 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 ..
Ähnliche Inhalte
Microsoft Office
Excel VBA Wert hochzählen
gelöst Frage von Florian86Microsoft Office1 Kommentar

Hallo, ich habe im VBA Code folgendes stehen Range("E10") = Range("E10") + 1 Jetzt ist der Wert bei schon ...

Batch & Shell

Per Batch Datei die Zahl jede Zell in einer Spalte um 10 verkleinern

gelöst Frage von ha5257Batch & Shell12 Kommentare

Guten Morgen, ich importiere jeden Tag automatisch eine Lagerbestandsliste(csv Datei) von meinem Lieferant mit einem Batch Datei. Die Batch ...

VB for Applications

Excel VBA - Worddokument mit Formatierung erzeugen und Zellen einfügen

Frage von chef1568VB for Applications2 Kommentare

Hallo, ich möchte aus Excel herraus ein Word Dokument erstellen um verschiedene Bereiche aus der Excel-Tabelle in das Dokument ...

Microsoft Office

Excel Tabellen Formatierung

Frage von MarabuntaMicrosoft Office4 Kommentare

Hallo, ich habe eine Tabelle die ich theoretisch auf 1/27 kürzen könnte, wenn ich wüsste wie ich es umformatiere ...

Neue Wissensbeiträge
Humor (lol)
Administrator.de Perlen
Tipp von DerWindowsFreak2 vor 1 TagHumor (lol)3 Kommentare

Hallo, Heute beim stöbern auf dieser Seite bin auf folgenden Thread aus dem Jahre 2006 gestossen: Was meint ihr? ...

Erkennung und -Abwehr
OpenSSH-Backdoor Malware erkennen
Tipp von Frank vor 2 TagenErkennung und -Abwehr

Sicherheitsforscher von Eset haben 21 Malware-Familien untersucht. Die Malware soll Hintertüren via OpenSSH bereitstellen, so dass Angreifer Fernzugriff auf ...

iOS
WatchChat für Whatsapp
Tipp von Criemo vor 5 TageniOS5 Kommentare

Ziemlich coole App für WhatsApp User in Verbindung mit der Apple Watch. Gibts für iOS sowohl als auch für ...

iOS
IOS hat nen Cursor!
Tipp von Criemo vor 6 TageniOS5 Kommentare

Nette Funktion im iOS. iPhone-Mauszeiger aktivieren „Nichts ist nerviger, als bei einem Tippfehler zu versuchen, den iOS-Cursor an die ...

Heiß diskutierte Inhalte
Festplatten, SSD, Raid
SSD zeigt falsche Werte
Frage von karl2014Festplatten, SSD, Raid25 Kommentare

Ich habe ein Problem mit der SSD in meinem Laptop mit Windows 10. Es ist eine 1Tb Platte die ...

Grafikkarten & Monitore
PCIe 1.0 Grafikkarte für 3840x2160
Frage von Windows10GegnerGrafikkarten & Monitore24 Kommentare

Hallo, mein Vater hat einen neuen Monitor gekauft, welcher eine native Auflösung von 3840*2160 hat. Diese muss jetzt auch ...

Windows 10
Windows Enterprise 1809 Eval nicht bootbar
Frage von Sunny89Windows 1022 Kommentare

Hallo zusammen, bevor ich mich jetzt noch stundenlang rumärger wollte ich euch fragen, ob Ihr die gleichen Probleme habt ...

Ubuntu
Installation freerdp 2.0.0-rc4
Frage von kristovUbuntu20 Kommentare

Hallo, möchte freerdp 2.0.0-rc4 auf linux mint 18.3 installieren, habe aber keine Ahnung, wie das funktioniert. freerdp 1.1 ist ...