wie kann ich aus 4 verschieden Blättern die Zahlenwerte in einen einzigen Array lesen?
Die Werte stehen immer in Spalte A von A1 bis A?
Danke Jochen
Sub rein_damit()
Dim arr() As Variant
Dim anzahl As Long
Dim i As Long, l As Long, x As Long
For i = 1 To 4
anzahl = anzahl + WorksheetFunction.CountA(Sheets(i).[a:a])
Next i
ReDim arr(anzahl)
i = 0
For l = 1 To 4
With Sheets(l)
For x = 1 To .[a65536].End(xlUp).Row
If Not IsEmpty(.Cells(x, 1)) Then
arr(i) = .Cells(x, 1)
i = i + 1
End If
Next x
End With
Next l
End Sub
Sub ArrayMulti()
Dim rng As Range
Dim arr As Variant
Dim tmp As String
Dim wks As Worksheet
Dim lastRow As Long
For Each wks In ThisWorkbook.Worksheets
lastRow = IIf(wks.Range("A65536") <> "", 65536, _
wks.Range("A65536").End(xlUp).Row)
arr = wks.Range("A1:A" & lastRow)
tmp = tmp & Join2(arr, ";")
Next
arr = Split(Left(tmp, Len(tmp) - 1), ";")
End Sub
Function Join2(field As Variant, Optional delimit As String) As String
'zweidimensionales array zu string umwandeln
Dim n As Long, m As Long
Dim temp As String
If delimit = "" Then delimit = " "
For n = LBound(field, 2) To UBound(field, 2)
For m = LBound(field, 1) To UBound(field, 1)
temp = temp & field(m, n) & delimit
Next
Next
Join2 = temp
End Function
Option Explicit
Sub ArrayMulti()
Dim rng As Range
Dim arr As Variant
Dim tmp As String
Dim wks As Worksheet
Dim lastRow As Long, n As Integer
Dim SheetArray As Variant
'Hier die Tabellennamen angeben!
SheetArray = Array("Tabelle1", "Tabelle2", "Tabelle3", "Tabelle4")
For n = 0 To UBound(SheetArray)
lastRow = IIf(Sheets(SheetArray(n)).Range("A65536") <> "", 65536, _
Sheets(SheetArray(n)).Range("A65536").End(xlUp).Row)
arr = Sheets(SheetArray(n)).Range("A1:A" & lastRow)
tmp = tmp & Join2(arr, ";")
Next
'Hier das komplette Array!
arr = Split(Left(tmp, Len(tmp) - 1), ";")
End Sub
Function Join2(field As Variant, Optional delimit As String) As String
'zweidimensionales array zu string umwandeln
Dim n As Long, m As Long
Dim temp As String
If delimit = "" Then delimit = " "
For n = LBound(field, 2) To UBound(field, 2)
For m = LBound(field, 1) To UBound(field, 1)
temp = temp & field(m, n) & delimit
Next
Next
Join2 = temp
End Function
Format(arr(x), "00000")