Range в массив

[Calvin]

Подскажите плиз, как значения в диапазоне А1:А100 быстрым способом представить в виде массива?

[Pavel55]

Наверное так

Код: Выделить всё

Sub Макрос1()
Dim myArray()  As Long 'в зависимости от значений
ReDim myArray(1 To 100)
Dim i&
    For i = 1 To 100
        myArray(i) = Cells(i, 1)
    Next i
End Sub

[Gloom]

Код: Выделить всё

Dim arr() As Variant
   
arr = Application.WorksheetFunction.Transpose(Range("A1:A100").Value)

[GSerg]

Это довольно сложно.

dim v as variant
v=range("a1:a100").value

Примерно так.

[KL]

еще варианты:

Код: Выделить всё

Sub test()
    Dim x1, x2, x3, y1, y2, y3, z1, z2, z3
    x1 = Range("A1:A100")
    x2 = Range("INDEX((A1:A100,B1:B100,C1:C100),,,2)")
    x3 = Range("OFFSET(A1:A100,2,1)")
    y1 = Evaluate("A1:A100")
    y2 = Evaluate("INDEX((A1:A100,B1:B100,C1:C100),,,2)")
    y3 = Evaluate("OFFSET(A1:A100,2,1)")
    z1 = [A1:A100]
    z2 = [INDEX((A1:A100,B1:B100,C1:C100),,,2)]
    z3 = [OFFSET(A1:A100,2,1)]
End Sub

[@Nik]

Эти все варианты закидывают диапазон в двуменрный массив. А как без цикла получить из этого одномерный массив ?

[tyomitch]

Пропатчить SAFEARRAY

[KL]

Эти все варианты закидывают диапазон в двуменрный массив. А как без цикла получить из этого одномерный массив ?

- Если диапазон двухмерный, то никак
если конечно речь не идет о простом отрубании второго измерения: x=Application.Index([A1:B100],,1)

- Если диапазон одномерный, то незачем

[@Nik]

Хочу ещё раз акцентировать внимание на один нюанс:
все вышеперечисленные варианты загоняют диапазон в ДВУМЕРНЫЙ массив, у которого длина второго измерения = 1, но обращаться к полученному массиву надо всё равно как к двумерному....! Х1(1,1); Х1(1,2) и т.д. При этом не получается воспользоваться, скажем, функцией Join() Мой вопрос касается именно функции Join() - каким синтаксисом можно сделать так, чтобы она восприняла полученный массив. Естесственно, вариант, когда в цикле пересылаются значения из двумерного массива в одномерный не допускается...
Другими словами мне надо любым возможным (но быстрым способом) сцепить значения диапазона

[KL]

Gloom уже некоторым образом дал решение, отвечая на вопрос Calvin.

Код: Выделить всё

Sub test()
    MsgBox Join(Application.Transpose(Range("A1:A10")), "")
    MsgBox Join(Application.Transpose(Application.Transpose(Range("A1:J1"))), "")
End Sub

Только учитывай три вещи:

1) для гориз. диапазонов транспонировать надо дважды

2) в некоторых версиях (по-моему 2000 и ранее) функция Transpose транспонирует не более 5461 элементов за раз.

3) функция Join() доступна, начиная с версии Excel2000

[@Nik]

Класс !!! Оригинальное решение.... Но это ещё не всё А как быть, если сцепляемые элементы лежат в формуле массива в одной ячейке ?

[@Nik]

У меня возник ещё один интересный вопрос.... Можно сказать обратная задача - массив в range.
Например есть формула:
{=если(A1:A10=55;A1:A10)}
Можно ли как-то сделать, чтобы возвращались ссылки а не значения ячеек ? Тогда, например, если этой формуле присвоить имя и обратиться к этому имени из ВБА, то получить сами ячейки из диапазона А1:А10 (объект Range).

[dormouse]

ещёб понять, что эта формула делает...

это имелось в виду?
ActiveCell.FormulaArray
или
ActiveCell.Formula

или чтоб программа читала формулу?
целый синтаксический анализатор надо писать

[@Nik]

Она выбирает цифры "55" из диапазона А1:А10, если они там есть. Но мне надо получить не сами значения, а ячейки, где они лежат....

[KL]

Она выбирает цифры "55" из диапазона А1:А10, если они там есть. Но мне надо получить не сами значения, а ячейки, где они лежат....

не знаю, то ли это, но...

Код: Выделить всё

Sub test()
    Dim strAddr As String
    Dim arrRef() As Variant
    Dim strRng As String
   
    strAddr = ActiveSheet.Range("A1:A10").Address(, , , True)
   
    arrRef = Application.Transpose(Evaluate("IF(" & strAddr & "=55,ADDRESS(ROW(" & strAddr & "),COLUMN(" & strAddr & ")),"""")"))
    'arrRef = Application.Transpose([IF(A1:A10=55,ADDRESS(ROW(A1:A10),COLUMN(A1:A10)),"")])
   
    strRng = Replace(Application.Trim(Join(arrRef, " ")), " ", ",")
   
    ActiveSheet.Range(strRng).Select
End Sub

(!!!) Если не ошибаюсь макс. длина текстовой строки интерпретируемой пропертью Range - 255 знаков.

P.S. Формула типа =IF(...,...,...) может возвращать ссылку только если условие проверяется для всего диапазона сразу:
=IF(A1=55,A1:A10) <- работает
=IF(A1:A10=55,A1:A10) <- не работает
Думаю, логика проста:
в первом случае результат либо диапазон (тип Range) либо логическое значение FALSE (тип Boolean)
во втором - результат cостоит из множества результатов и может содержать более одного типа одновременно, а значит автоматически массив (Array) типа Variant

[KL]

А как быть, если сцепляемые элементы лежат в формуле массива в одной ячейке ?

Думаю, что в этом случае единственный способ это брать из ячейки формулу и интерпретировать ее из-под VBA с пом. функции Evaluate().

[@Nik]

Большое спасибо.... Ты как всегда раскрываешь глаза на широчайшие возможности ВБА
А я, вот, ещё попробовал так: задал имя формуле:
=ДВССЫЛ(ЕСЛИ(Лист1!$A$1:$A$10=55;АДРЕС(СТРОКА(Лист1!$A$1:$A$10);СТОЛБЕЦ(Лист1!$A$1:$A$10));"A1"))
Если ввести формулу в ячейку - вроде как работает.... даже можно поверху сделать СМЕЩ(....). Но если ввести в область имён - выдаёт только ссылку на первую ячейку

А вообще задача была такая: есть числовой столбец, есть поле для фильтра по этому столбцу, т.е. вводимые в поле цифры должны искаться внутри значений столбца (типа подстроки). Так вот, обычным автофильтром воспользоваться нельзя (подстроку внутри числового столца он не ищет). Вышел из положения так: рядом сделал дополнительный столбец с формулой:
=ЕСЛИ(ЕОШ(ПОИСК(ИскомоеЗнач;L12&""));"";1), где "L" - исходный числовой столбец
а потом автофильтр с условием "=1" выдавал мне то, что надо

[KL]

Большое спасибо.... Ты как всегда раскрываешь глаза на широчайшие возможности ВБА
А я, вот, ещё попробовал так: задал имя формуле:
=ДВССЫЛ(ЕСЛИ(Лист1!$A$1:$A$10=55;АДРЕС(СТРОКА(Лист1!$A$1:$A$10);СТОЛБЕЦ(Лист1!$A$1:$A$10));"A1"))
Если ввести формулу в ячейку - вроде как работает.... даже можно поверху сделать СМЕЩ(....). Но если ввести в область имён - выдаёт только ссылку на первую ячейку

Функция ДВССЫЛ() действительно теряет способность работать с матрицами при использовании ее в именах или VBA. Однако на листе эта формула практически уникальна - она может возвращать массив значений несмежных ячеек(!!!) Такое еще возможно только с функцией СМЕЩ():
=ДВССЫЛ({"A4";"A7";"A10"})
=СМЕЩ(A1;{3;6;9};0)

Твою формулу можно еще усложнить, чтобы она возвращала массив значений только найденных ячеек:
{=ДВССЫЛ("A"&НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$10=55;СТРОКА($A$1:$A$10));СТРОКА(ДВССЫЛ("1:"&СЧЁТЕСЛИ($A$1:$A$10;55)))))}

Задачу твою я, если честно, не понял Если не сложно, повесь файл с примером.

[@Nik]

Да, можно и так... мы уже тобой научены

А задачка вот:

[KL]

А задачка вот:

Такой вариант без доп. столбца (вероятно будет несколько медленнее, чем с доп. столбцом)

[@Nik]

Мдя.... мне ещё учиться и учиться..... Отличное решение И не надо заботиться о распространении формулы при обновлении таблицы и изменении кол-ва строк