Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Daten aus Matrixvariable im richtigen Format

Daten aus Matrixvariable im richtigen Format
22.03.2013 11:07:05
Peter
Guten Tag
Mit nachfolgendem Makro lege ich mir das Worksheet "Index" an und fülle die Spalte A und gegebenenfalls auch die Spalten C, E, G und I mit Werten ab.
In Spalte C wird eine Nummer abgefüllt. Diese Nummer ermittle ich wie folgt:
lngNummer = IFF(blnNumerisch, Mid(.Worksheets(lngIndex).Range("A2"), 10, 99) * 1, "")
Obschohn es sich hier um eine Longvariable handelt, erhalte ich als Ergebnis eine Zahl als Text
Weshalb ist das so und was muss ich vorkehren, dass ich eine Zahl erhalte (nicht im Textformat)?
In Spalte E erhalte ich ein Datum im Textformat. Das ist klar, da ich das Datum einem Teilstring entspricht.
Was muss ich vorkehren, damit ich in Spalte E ein Datum im Datumsformat "DD.MM.JJJJ" erhalte?
Gruss, Peter
Sub TabellenIndex()
Dim vntSheets() As String, lngIndex As Long, WkbThis As Excel.Workbook, objSh As Worksheet,  _
blnNumerisch, strText As String, lngNummer As Long
Set WkbThis = ThisWorkbook
Application.DisplayAlerts = False
On Error Resume Next
WkbThis.Sheets("Index").Delete
Set objSh = WkbThis.Worksheets.Add(after:=Sheets("Daten"))
objSh.Name = "Index"
With WkbThis
''' Matrix anlegen für Anzahl Zeilen = Anzahl Worksheets und 9 Spalten
ReDim vntSheets(1 To .Worksheets.Count, 1 To 9)
''' Alle Worksheets abarbeiten
For lngIndex = 1 To .Worksheets.Count
''' Abfragen, ob es sich beim abzuarbeitenden Worksheet um eine ALUX-Tabelle  _
handelt (Bezeichnung ist ab dritter Stelle numerisch)
blnNumerisch = IsNumeric(Mid(.Worksheets(lngIndex).Name, 3, 99))
''' Bei numerischen Worksheets aus Zelle A2 die XNummer auslesen
''' (muss mit 1 multipliziert werden, damit aus dem String ein numerischer Wert  _
wird)
lngNummer = IFF(blnNumerisch, Mid(.Worksheets(lngIndex).Range("A2"), 10, 99) * 1, "") _
''' Bei numerischen Worksheets aus Zelle A2 die ersten 8 Zeichen (=Datumsstring)  _
auslesen
strText = IFF(blnNumerisch, Left(.Worksheets(lngIndex).Range("A2"), 8), "")
'' Spalte 1 wird der Name des Worksheets übergeben
vntSheets(lngIndex, 1) = .Worksheets(lngIndex).Name
'' bei ALUX-Tabellen wird Spalte 3 die XNummer übergeben
vntSheets(lngIndex, 3) = IFF(blnNumerisch, lngNummer, "")
''' bei ALUX-Tabellen wird Spalte 5 das Datum as String übergeben
vntSheets(lngIndex, 5) = IFF(blnNumerisch, strText, "")
'' bei ALUX-Tabellen wird Spalte 7 der Fondsname übergeben
vntSheets(lngIndex, 7) = IFF(blnNumerisch, Application.WorksheetFunction.VLookup( _
lngNummer, Range("Daten2"), 5, False), "")
'' bei ALUX-Tabellen wird Spalte 9 der Klassennamen übergeben
vntSheets(lngIndex, 9) = IFF(blnNumerisch, Application.WorksheetFunction.VLookup( _
lngNummer, Range("Daten2"), 6, False), "")
'' Spalten 2, 4, 6, 8 bleiben leer - damit Worksheet "Index" besser lesbar ist
Next
End With
'' Sheets("Index").Cells.ClearContents   'nicht notwendig, da Worksheet immer neu angelegt wird
With Sheets("Index")
.Range("A:I").ClearContents
.Range("A1").Resize(UBound(vntSheets, 1), 9) = vntSheets
.Columns.Range("A:I").ColumnWidth = 1.5
'' .Rows.Range("1:2").RowHeight = 10  ‘’’Zeilenhöhe anpassen entfällt
.Columns.AutoFit
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
WkbThis.Worksheets("Cockpit").Activate
End Sub

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Daten aus Matrixvariable im richtigen Format
22.03.2013 11:49:51
Rudi
Hallo,
mit On Error Resume Next im gesamten Code findest du den Fehler nie.
Schalt's ab, sobald es nicht mehr gebraucht wird.
Gruß
Rudi

ein paar Hinweise
22.03.2013 14:00:44
Erich
Hi Peter,
die Zeile
lngNummer = IFF(blnNumerisch, Mid(.Worksheets(lngIndex).Range("A2"), 10, 99) * 1, "")
(IFF wäre erst mal zu ersetzen durch Iif)
ergibt einen Laufzeitfehler, wenn blnNumerisch = False ist, denn
lngNummer ist vom Typ Long - damit kann man dieser Variablen keinen Wert "" zuweisen, allenfalls 0.
"Obschohn es sich hier um eine Longvariable handelt, erhalte ich als Ergebnis eine Zahl als Text"
Meinst du mit "Ergebnis" den Wert in vntSheets()?
Das Array vntSheets() hast du als String deklariert, es enthält also nur Texte.
Du müsstest vntSheets() als Variant deklarieren.
Folgende Routine läuft - in meinem Grobtest ohne passende Umgebung - ohne Fehler durch:

Option Explicit
Sub TabellenIndex()
Dim vntSheets(), lngIndex As Long, WkbThis As Excel.Workbook, objSh As Worksheet
Dim blnNumerisch, strText As String, lngNummer As Long
Set WkbThis = ThisWorkbook
Application.DisplayAlerts = False
On Error Resume Next
WkbThis.Sheets("Index").Delete
On Error GoTo 0
Set objSh = WkbThis.Worksheets.Add(after:=Sheets("Daten"))
objSh.Name = "Index"
With WkbThis
''' Matrix anlegen für Anzahl Zeilen = Anzahl Worksheets, 9 Spalten
ReDim vntSheets(1 To .Worksheets.Count, 1 To 9)
''' Alle Worksheets abarbeiten
For lngIndex = 1 To .Worksheets.Count
''' Abfragen, ob es sich beim abzuarbeitenden Worksheet um eine
'   ALUX-Tabelle handelt (Bezeichnung ist ab dritter Stelle numerisch)
blnNumerisch = IsNumeric(Mid(.Worksheets(lngIndex).Name, 3, 99))
''' Bei numerischen Worksheets aus Zelle A2 die XNummer auslesen
''' (muss mit 1 multipliziert werden, damit aus dem String
'''  ein numerischer Wert wird)
If blnNumerisch Then
lngNummer = Mid(.Worksheets(lngIndex).Range("A2"), 10, 99) * 1
Else
lngNummer = 0
End If
''' Bei numerischen Worksheets aus Zelle A2 die ersten 8 Zeichen
''' (=Datumsstring) auslesen
strText = IIf(blnNumerisch, Left(.Worksheets(lngIndex).Range("A2"), 8), "")
'' Spalte 1 wird der Name des Worksheets übergeben
vntSheets(lngIndex, 1) = .Worksheets(lngIndex).Name
'' bei ALUX-Tabellen wird Spalte 3 die XNummer übergeben
vntSheets(lngIndex, 3) = IIf(blnNumerisch, lngNummer, "")
''' bei ALUX-Tabellen wird Spalte 5 das Datum as String übergeben
vntSheets(lngIndex, 5) = IIf(blnNumerisch, strText, "")
'' bei ALUX-Tabellen wird Spalte 7 der Fondsname übergeben
If lngNummer > 0 Then
vntSheets(lngIndex, 7) = Application.WorksheetFunction.VLookup( _
lngNummer, Range("Daten2"), 5, False)
vntSheets(lngIndex, 9) = Application.WorksheetFunction.VLookup( _
lngNummer, Range("Daten2"), 6, False)
End If
'         vntSheets(lngIndex, 7) = IIf(blnNumerisch, Application.WorksheetFunction.VLookup( _
'            lngNummer, Range("Daten2"), 5, False), "")
'                              '' bei ALUX-Tabellen wird Spalte 9 der Klassennamen übergeben
'         vntSheets(lngIndex, 9) = IIf(blnNumerisch, Application.WorksheetFunction.VLookup( _
'            lngNummer, Range("Daten2"), 6, False), "")
Next
End With
With Sheets("Index")
.Range("A:I").ClearContents
.Range("A1").Resize(UBound(vntSheets, 1), 9) = vntSheets
.Columns.Range("A:I").ColumnWidth = 1.5
.Columns.AutoFit
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
'   WkbThis.Worksheets("Cockpit").Activate
End Sub
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
und noch eine Version
22.03.2013 17:42:28
Erich
Hi Peter,
hier habe ich noch ein wenig im Code "rumgespielt":

Sub TabellenIndex2()
Dim vntSheets(), lngInd As Long, objSh As Worksheet, wks As Worksheet
Dim lngNummer As Long, strT As String
With ThisWorkbook
Application.DisplayAlerts = False: On Error Resume Next
.Sheets("Index").Delete                             ' lösche altes Blatt "Index"
On Error GoTo 0: Application.DisplayAlerts = True
Set objSh = .Worksheets.Add(after:=.Sheets("Daten")) ' neues Blatt "Index"
''' Matrix anlegen: Anzahl Zeilen = Anzahl Worksheets, 9 Spalten
ReDim vntSheets(1 To .Worksheets.Count, 1 To 9)
''' Alle Worksheets abarbeiten
For lngInd = 1 To .Worksheets.Count
lngNummer = 0
Set wks = .Worksheets(lngInd)
'' Spalte 1 wird der Name des Worksheets übergeben
vntSheets(lngInd, 1) = wks.Name
''' Abfragen, ob es sich beim abzuarbeitenden Worksheet um eine
''' ALUX-Tabelle handelt (Bezeichnung ist ab 3. Stelle numerisch)
If IsNumeric(Mid(wks.Name, 3, 99)) Then
''' Bei numerischen Worksheets aus Zelle A2 die XNummer auslesen
''' (muss mit 1 multipliziert werden, damit aus dem String
'''  ein numerischer Wert wird)
strT = Mid(wks.Range("A2"), 10, 99)
If IsNumeric(strT) Then lngNummer = strT * 1
'' bei ALUX-Tabellen wird Spalte 3 die XNummer übergeben
vntSheets(lngInd, 3) = lngNummer
''' Bei numerischen Worksheets aus Zelle A2 die ersten 8 Zeichen
''' (=Datumsstring) auslesen
''' bei ALUX-Tabellen wird Spalte 5 das Datum as String übergeben
vntSheets(lngInd, 5) = Left(wks.Range("A2"), 8)
'' bei ALUX-Tabellen wird Spalte 7 der Fondsname übergeben
If lngNummer > 0 Then
vntSheets(lngInd, 7) = Application.WorksheetFunction.VLookup( _
lngNummer, .Range("Daten2"), 5, False)
'' bei ALUX-Tabellen wird Spalte 9 der Klassennamen übergeben
vntSheets(lngInd, 9) = Application.WorksheetFunction.VLookup( _
lngNummer, .Range("Daten2"), 6, False)
End If
End If
Next lngInd
With objSh
.Name = "Index"
.Range("A:I").ClearContents
.Range("A1").Resize(UBound(vntSheets), UBound(vntSheets, 2)) = vntSheets
.Columns.Range("A:I").ColumnWidth = 1.5
.Columns.AutoFit
End With
'   Application.ScreenUpdating = True
.Worksheets("Cockpit").Activate
End With
End Sub
Viel Spaß damit und ein schönes Wochenende!
Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: und noch eine Version
23.03.2013 10:19:26
Peter
Hallo Erich
Vielen Dank. Das hat so funktioniert. Nur bei den beiden VLookup-Abfragen musste ich das Worksheet angeben: Worksheets("Daten").Range("Daten2")
Eine Frage habe ich noch. Die Ausgabe in Spalte E (Code: vntSheets(lngInd, 5) = Left(wks.Range("A2"), 8) liefert mir in der Zieltabelle das Datum im Textformat.
Wie kriege ich das in ein normales Datumsformat?
Nochmals besten Dank und Gruss, Peter

AW: und noch eine Version
23.03.2013 10:20:58
Hajo_Zi
Multiplikation mit 1 und Datumsformat.

AW: und noch eine Version
23.03.2013 10:21:47
Hajo_Zi
oder cdate( Left(wks.Range("A2"), 8))
Gruß Hajo

Anzeige
AW: und noch eine Version
23.03.2013 13:51:34
Erich
Hi Peter,
da würde ich sicherheitshalber vorab prüfen, ob der Wert für vntSheets(lngInd, 5)
in ein Datum umgewandelt werden kann.
"Daten2" ist wohl der Name eines Bereichs im Blatt "Daten". Das war bislang nicht bekannt. :-)
Mein Vorschlag (mit Hajos CDate):

vntSheets(lngInd, 3) = lngNummer
''' Bei numerischen Worksheets aus Zelle A2 die ersten 8 Zeichen
''' (=Datumsstring) auslesen
''' bei ALUX-Tabellen wird Spalte 5 das Datum as String übergeben
vntSheets(lngInd, 5) = Left(wks.Range("A2"), 8)
' Wenn der Wert in ein Datum umgewandelt werden kann, do it
If IsDate(vntSheets(lngInd, 5)) Then vntSheets(lngInd, 5) = _
CDate(vntSheets(lngInd, 5))
If lngNummer > 0 Then
'' bei ALUX-Tabellen wird Spalte 7 der Fondsname übergeben
vntSheets(lngInd, 7) = Application.WorksheetFunction.VLookup( _
lngNummer, .Worksheets("Daten").Range("Daten2"), 5, False)
'' bei ALUX-Tabellen wird Spalte 9 der Klassennamen übergeben
vntSheets(lngInd, 9) = Application.WorksheetFunction.VLookup( _
lngNummer, .Worksheets("Daten").Range("Daten2"), 6, False)
End If
Noch eine Bemerkung: Es geht bei dem Datum nicht ums Format, sondern um den Wert.
"23.03.13" ist eine Zeichenkette, bestehend aus 8 Zeichen, darunter zwei Punkten.
Das Datum 23.03.13 (ausführlich 23.03.2013) ist die Zahl 41356, die per Zahlenformat als Datum dargestellt werden kann.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: und noch eine Version
23.03.2013 17:28:58
Peter
Hallo Hajo und Erich
Vielen Dank!
Ich erlaube mir noch eine Anschlussfrage:
Kann ich nicht analog
If IsDate(vntSheets(lngInd, 5)) Then vntSheets(lngInd, 5) = _
CDate(vntSheets(lngInd, 5))
den numerischen Text umwandeln?
If IsNumeric(vntSheets(lngInd, 3)) Then vntSheets(lngInd, 3) = _
CDbl(vntSheets(lngInd, 3))
Gruss, Peter

Anschlussantwort :-)
23.03.2013 19:48:46
Erich
Hi Peter,
im Prinzip ja, aber:
- passender CLng statt CDbl (CDbl geht natürlich auch)
- mit "vntSheets(lngInd, 3) = lngNummer" wird vntSheets(lngInd, 3) bereits eine Zahl (Long) zugewiesen.
- lngNummer wurde vorher mit "lngNummer = strT * 1" berechnet, das ist so gut wie mit CLng.
- Den Wert von vntSheets(lngInd, 3) auch in der Variablen lngNummer zu haben, ist nicht unpraktisch,
da lngNummer danach noch mehrfach verwendet wird. "lngNummer" ist im Code einfach sprechender
als vntSheets(lngInd, 3).
Erscheint in der Tabelle hier vielleicht ein Text? Dann könnte es daran liegen,
dass die entsprechende Zelle mit Zahlformat "Text" formatiert ist.
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

Anzeige
AW: Anschlussantwort :-)
25.03.2013 23:33:00
Peter
Hallo Erich
Hier ist noch meine Rückmeldung:
Es ist mir nicht gelungen, die Werte der Variable vntSheets so zu übergeben, dass ich letztendlich in Spalte C eine Zahl und in Spalte E ein Datum erhalte (das nicht nur so aussieht, sondern auch das entsprechende Zahlenformat hat.
vntSheets(lngIndex, 3) = IIf(blnNumerisch, lngNummer, "")
If IsNumeric(vntSheets(lngIndex, 3)) Then vntSheets(lngIndex, 3) = CLng(vntSheets(lngIndex, 3))
''' bei ALUX-Tabellen wird Spalte 5 das Datum as String übergeben
vntSheets(lngIndex, 5) = IIf(blnNumerisch, strText, "")
If IsDate(vntSheets(lngIndex, 5)) Then vntSheets(lngIndex, 5) = CDate(vntSheets(lngIndex, 5))
'' bei ALUX-Tabellen wird Spalte 7 der Fondsname übergeben
Deshalb formatiere ich jetzt die entsprechenden Zellen mit diesem Code nach Übergabe in die Tabelle "Index" nachdem ich weiter vorne die Anzahl Tabellen einer Variable übergeben habe (lngWS = .Worksheets.Count):
For Each rCell In Sheets("Index").Range("C1:C" & lngWS)
If rCell.Value "" Then rCell.Value = rCell.Value * 1
Next
For Each rCell In Sheets("Index").Range("E1:E" & lngWS)
If rCell.Value "" Then rCell.Value = CDate(rCell.Value)
If rCell.Value "" Then rCell.NumberFormat = "DD.MM.YYYY"
Next
So klappt jetzt alles. Da es in der Regel maximal 60 Zeilen abzuarbeiten gibt, ist dies auch zeitlich kein Problem.
Nochmals vielen Dank für die Unterstützung!
Gruss, Peter
Anzeige

305 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige