Добавить рабочие листы в массив с помощью VBA

avatar
kitsana
8 августа 2021 в 16:40
111
2
0

Я практиковался в VBA в течение одного месяца, и у меня возникла проблема. Я хочу создать массив из данных на листе с помощью этого цикла, но он не работает. У меня есть 6 рабочих листов, но он может получить только один рабочий лист в массив. Я думаю, что проблема в этом цикле - это DataArray, но я не могу ее решить. 

Sub LoopByArray()

    Dim ws As Worksheet
    Dim LastRow As Long
    Dim LastCol As Long
    Dim DataArray() As Variant
    Dim Sheetnum As String
    Dim SheetNames()
    Dim i As Long

    SheetCount = ActiveWorkbook.Sheets.Count
    ReDim SheetNames(1 To SheetCount)

    For i = 1 To SheetCount
        SheetNames(i) = ActiveWorkbook.Sheets(i).Name
        Debug.Print SheetNames(i) 
        Sheetnum = i
        Set ws = worksheets(SheetNames(i))
        LastRow = ThisWorkbook.Sheets(SheetNames(i)).Range("A" & Rows.Count).End(xlUp).Row

        Debug.Print LastRow

        LastCol = ThisWorkbook.Sheets(SheetNames(i)).Cells(1, ThisWorkbook.Sheets(SheetNames(i)).Columns.Count).End(xlToLeft).Column

        Debug.Print LastCol

        ColLetter = GetColumnLetter(LastCol)
        DataArray = ThisWorkbook.Sheets(SheetNames(i)).Range("A1" & ":" & ColLetter & LastRow).Value

    Next i

End Sub

Function GetColumnLetter(colNum As Long) As String
    
    Dim vArr
    vArr = Split(Cells(1, colNum).Address(True, False), "$")
    GetColumnLetter = vArr(0)

End Function
Источник
Tim Williams
8 августа 2021 в 17:31
0

Вам нужен один массив, содержащий данные со всех листов? Как это должно быть организовано, если (например) разные листы имеют разное количество строк или столбцов?

kitsana
8 августа 2021 в 18:52
0

Я думаю, что мне нужно создать больше массивов, чтобы получить другие рабочие листы, но я не знаю, как добавить их в этот цикл.

kitsana
8 августа 2021 в 19:04
0

Можно ли создать цикл или другие функции для определения переменных массива на основе количества листов в VBA?

CDP1802
8 августа 2021 в 20:24
2

Что вы собираетесь делать с массивами?

kitsana
9 августа 2021 в 02:09
0

Я пытаюсь получить данные и использовать vlookup в массиве, потому что думаю, что это будет быстрее, чем на листе.

Ответы (2)

avatar
CDP1802
9 августа 2021 в 09:46
0

Вы можете сделать dataArray() одномерным массивом.

Option Explicit

Sub FillArrays()
    
    Dim dataArray(), wb As Workbook, ws As Worksheet
   
    Set wb = ThisWorkbook
    ReDim dataArray(wb.Sheets.Count)

    For Each ws In wb.Sheets
        dataArray(ws.Index) = ws.UsedRange.Value
    Next
    Debug.Print dataArray(3)(1, 1) 'Sheets(3).Cells(1, 1)

End Sub
avatar
Tin Bum
8 августа 2021 в 19:32
0

Вот несколько возможностей для работы

Public Sub Shts2Arrays()
  Dim ShtAR1() As Variant, ShtAR2() As Variant, ShtAR3() As Variant
  Dim i As Integer, J As Integer
  
  ShtAR1 = Sheets(1).UsedRange.Value
  ShtAR2 = Sheets(2).UsedRange.Value
  ShtAR3 = Sheets(3).UsedRange.Value
  
  For i = LBound(ShtAR1) To UBound(ShtAR1)
     For J = 1 To Sheets(1).UsedRange.Columns.Count
        Debug.Print i, J, ShtAR1(i, J)
     Next J
  Next i
  
  For i = LBound(ShtAR2) To UBound(ShtAR2)
     For J = 1 To Sheets(2).UsedRange.Columns.Count
        Debug.Print i, J, ShtAR2(i, J)
     Next J
  Next i
  
  For i = LBound(ShtAR3) To UBound(ShtAR3)
     For J = 1 To Sheets(3).UsedRange.Columns.Count
        Debug.Print i, J, ShtAR3(i, J)
     Next J
  Next i
  
End Sub

В дальнейшем массивы транспонируются с помощью Application.Transpose, в остальном то же самое

Public Sub TransposeShts2Arrays()
  Dim ShtAR1() As Variant, ShtAR2() As Variant, ShtAR3() As Variant
  Dim i As Integer, J As Integer
  
  ShtAR1 = Application.Transpose(Sheets(1).UsedRange.Value)
  ShtAR2 = Application.Transpose(Sheets(2).UsedRange.Value)
  ShtAR3 = Application.Transpose(Sheets(3).UsedRange.Value)
  
  For i = LBound(ShtAR1) To UBound(ShtAR1)
     For J = 1 To Sheets(1).UsedRange.Columns.Count
        Debug.Print i, J, ShtAR1(i, J)
     Next J
  Next i
  
  For i = LBound(ShtAR2) To UBound(ShtAR2)
     For J = 1 To Sheets(2).UsedRange.Columns.Count
        Debug.Print i, J, ShtAR2(i, J)
     Next J
  Next i
  
  For i = LBound(ShtAR3) To UBound(ShtAR3)
     For J = 1 To Sheets(3).UsedRange.Columns.Count
        Debug.Print i, J, ShtAR3(i, J)
     Next J
  Next i
  
End Sub