ReDim и Subscript out of range (Error 9)

[oldfatham]

Здравствуйте.
Первый раз взял в руки VBA, да и вообще программировал мало.

Пытаюсь добавить массиву второе измерение с сохранением.
И не понимаю, где и что выходит за диапазон размерности.
Так как же его переопределить?


Watch : + : Agents : : Variant/Variant(0 to 51)

Sub DoAll_Click()
Dim Agents() As Variant


With ActiveSheet.UsedRange
    iLastRow = .Row + .Rows.Count - 2 
'Количество строк в файле

End With

n = 0
For i = 1 To iLastRow
    ActiveSheet.Range("K" & i).Activate
    iCount = Application.CountA(Rows(ActiveCell.Row)) 
'Проверяет, пустая строка или нет
    
        iText = Range("K" & i).Text
        If iText <> "" Or iText <> Null Then
            If Trim(Range("C" & i).Text) = "Поставщик услуг" Then
                      ReDim Preserve Agents(n)
                      sAgent = Trim(Range("K" & i).Text)
                      Agents(n) = sAgent
                      n = n + 1
                      
                      
             End If
        End If
    
Next 
'Конец цикла сбора агентов

For i = LBound(Agents) To UBound(Agents)
'Удаление дубликатов в массиве агентов (заменяю на пустую строку, из-за Out of Range если делать ReDim)
            sAgentToCheck = Agents(i)

            For j = i + 1 To UBound(Agents)
                If sAgentToCheck = Agents(j) Then
                Agents(j) = ""
                'ReDim Preserve Agents(UBound(Agents) - 1)
                End If

                Next



        Next
l = UBound(Agents())

'Имеем одномерный массив с названиями агентов
'Пытаемся добавить второе измерение для присвоения суммы выплат

ReDim Preserve Agents(l, 1) 'Здесь выскакивает Error 9, Out of Range

    
    Range("A1").Activate
   
End Sub

[SAS888]

Функция ReDim Preserve способна изменять лишь количество элементов последней размерности. Изменить одномерный на двумерный массив нельзя. Если хочется, то можно сразу объявить двумерный массив, затем применять ReDim Preserve, предварительно (ели нужно) пользоваться транспонированием. Но, по-моему, есть более рациональные способы. Тем более, что при каждом ReDim Preserve, все элементы массива перезаписываются. Это в данном случае ни к чему.

P.S. А почему сразу вместо ReDim Agents(n) не использовать ReDim Agents(n, 1)? И для чего это нужно в дальнейшем?

[oldfatham]

Спасибо, что откликнулись.

Извиняюсь за ламерство.

1) "Функция ReDim Preserve способна изменять лишь количество элементов последней размерности."
В случае Агенты(1,2) можно лишь увеличить 2-ку на большую? А если я хочу сделать далее Агенты(10,2)?

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

3) В идеале хотел сделать массив Агенты(н,2). Где н - постоянно меняется по мере обработки файла. Количество агентов заранее неизвестно, файлы устроены одинаково, но разного размера.
н - это столбец А, 2 - это столбец В (я правильно понимаю? а то уже парюсь)
Столбец А - название Агента, столбец В - сумма.

Хотелось бы сделать так.
Объявляем массив Агенты()
Цикл до последней строки на листе
При каждом нахождении агента, добавляем строку в массив, записываем агента в столбец А.
ReDim Agents(n,2) - добавляем строку в массив
n=n+1
Далее по каждому агенту считываем его сумму и дописываем в столбец В.

ReDim может добавлять строку в двумерный массив?
Как это правильно сделать?

А|B
-----
A1|34
A2|25
A3|62
A4|99
A5|43

[SAS888]

1) Чтобы в двумерном массиве изменить количество элементов 1-й размерности, сохранив значения, нужно транспонировать массив (поменять местами размерности), примерить ReDim Preserve, затем транспонировать обратно. Например:

Option Base 1
Sub test()
    Dim Agents()
    ReDim Agents(1, 2)
    MsgBox "Размерность массива (" & UBound(Agents, 1) & ", " & UBound(Agents, 2) & ")"
    Agents = Application.Transpose(Agents)
    ReDim Preserve Agents(2, 10)
    Agents = Application.Transpose(Agents)
    MsgBox "Размерность массива (" & UBound(Agents, 1) & ", " & UBound(Agents, 2) & ")"
End Sub

2) и 3)

Хотелось бы сделать так.
Объявляем массив Агенты()
Цикл до последней строки на листе
При каждом нахождении агента, добавляем строку в массив, записываем агента в столбец А.
ReDim Agents(n,2) - добавляем строку в массив
n=n+1
Далее по каждому агенту считываем его сумму и дописываем в столбец В.

А я бы сделал по-другому:
1. Определяем последнюю строку листа (пусть это N).
2. Определяем размерность массива как Agents(N,2). (При таком подходе, лучше установить опцию Option Base 1).
3. В цикле делаем то, что Вам требуется, и при нахождении агента, записываем его в Agents(m, 1) и инкременируем указатель m=m+1. Обратите внимание, что m - это не счетчик цикла, а указатель массива, который перед циклом нужно установить в m=1 и увеличивать только в случае нахождении агента.
4. Далее по каждому агенту считываем его сумму и записываем в соответствующий текущему агенту элемент второй размерности массива. Например, для агента Agents(q, 1) сумму пишем в Agents(q, 2).
Естественно, что скорее всего, останутся пустые элементы массива. Но, во-первых, при работе с массивом всегда можно проверить элемент на "пустоту", во-вторых, такой способ не требует ReDim Preserve, т.е. каждый раз перезаписывать все элементы массива, что тормозит.

Это один из возможных вариантов. Уверен, что Вашу задачу можно решить и более оптимальным способом. Так, например, одной командой сформировать массив из значений ячеек требуемого диапазона рабочего листа, затем проделать все операции внутри этого массива, т.е. в VB, не обращаясь к ячейкам, что существенно (раз в 100) быстрее. Затем, также одной командой, вставить обработанный массив в требуемый диапазон рабочего листа.
Если хотите оптимизации - прикрепите пример файла с данными и подробным описанием задачи.

[oldfatham]

Спасибо.
Пойду подумаю над Вашими словами.
Попытаюсь написать. Если застряну, выложу пример листа.
А то, если не сам напишу, буду считать, что с задачей не справился

[oldfatham]

SAS888, по вашему совету все загнал в массивы и там лопатил.
Работает нормально.
Спасибо за помощь