VBA: фильтрация списка и сохранение результата в виде именованных диапазонов

У меня есть следующий список:

введите здесь описание изображения

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

Например, именованными диапазонами будут Лондон, содержащие ячейки B2 и B7 и т. д.

ИЗМЕНИТЬ:

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

введите здесь описание изображения

Sub NamedRange()

Dim arr() As Variant

arr = Sheet1.Range("D2:D4").Value

    Dim i As Integer
    Dim j As Integer
    Dim Name As String
    Dim k1 As Range, k2 As Range

    Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row

    i = 0

    Do While i < 4
    Name = arr(i)
    For j = 1 To Lastrow
    k1 = Match(arr(i), Cells(i, 1).Value, 0)
    k2 = Union(k1, k2)

    Next j

    Range(k2).Select
    Application.Goto Reference:=arr(i)

    Loop


End Sub

EDIT2: потратил два часа, пытаясь понять функцию AdvancedFilter. Сделал это с помощью опции x1FilterCopy, но таким образом весь мой лист Excel будет в беспорядке. Есть ли способ фильтрации и просто сохранения отфильтрованного диапазона в переменной. Боже, VBA, почему ты сделан таким?


named-ranges vba
person Helen    schedule 23.10.2015    source источник
comment
Вы пробовали что-нибудь?   -  person Scott Craner    schedule 23.10.2015
comment
@ Скотт Кранер: Да, конечно. Но я действительно не знаю, как атаковать это. Я предполагаю, что мне нужно выяснить, как хранить каждый отдел в некотором массиве (как я не уверен), а затем сделать цикл for для каждого объекта в массиве, где я каким-то образом фильтрую нужные ячейки из первого столбца и сохраняю то, что Я отфильтровал в именованном диапазоне. Я просто совершенно новичок в этом VBA и не могу ясно мыслить.   -  person Helen    schedule 23.10.2015
comment
Это именно то, как это сделать. Так что погуглите каждую часть по отдельности, попробуйте собрать их вместе. Затем вернитесь со своим кодом, когда столкнетесь с конкретной проблемой. Примечание. Именованный диапазон будет работать до тех пор, пока кто-то не отсортирует данные, после чего вам нужно будет переименовать диапазоны. поэтому вы также захотите искать события изменения рабочего листа.   -  person Scott Craner    schedule 23.10.2015
comment
@ScottCraner: тогда я буду гуглить. Что вы имеете в виду, пока кто-то не отсортирует данные...? Именованный диапазон — это просто строка с кучей ячеек, которые вы хотите запомнить, верно?   -  person Helen    schedule 23.10.2015
comment
Да, но что происходит, когда лист, на котором существуют данные, отсортирован, именованный диапазон остается ссылающимся на те же ячейки на листе, но данные в этих ячейках теперь разные. Тем самым испортив вывод. В зависимости от желаемого результата вы можете попробовать другой метод.   -  person Scott Craner    schedule 23.10.2015
comment
@ScottCraner Я пробовал, как вы видите выше, но я просто не знаю ... Я не знаю, какие функции использовать или как их использовать ...   -  person Helen    schedule 26.10.2015

Ответы (1)


arrow_upward
1
arrow_downward

Попробуй это.

Обратите внимание, что, как сказано выше, использование именованных диапазонов таким образом, вероятно, не лучший подход.

Возможно, будет лучше объяснить вашу конечную цель (потому что вы можете фильтровать данные и манипулировать ими оттуда).

Как говорится, это делает то, что вы ищете.

Для каждой ячейки в B2:B10 мы увидим, существует ли именованный диапазон для этого значения.

Если именованный диапазон не существует, мы создаем его.

Если он существует, мы объединяем два диапазона.

Затем мы можем щелкнуть наши отделы и выбрать диапазоны, захватив их значение.

Скопируйте и вставьте этот макрос и запустите NameTheRanges

Затем вы можете добавить код события на первый лист, чтобы выбрать диапазоны при нажатии на отдел.

Sub NameTheRanges()

ClearAllNamedRanges 
Dim c As Range
For Each c In Range("B2:B10")
    If Not DoesNamedRangeExist(c.Value) Then
        c.Offset(0, -1).Name = c.Value
    Else
        Union(Range(c.Value), c.Offset(0, -1)).Name = c.Value
    End If
Next c

End Sub

Function DoesNamedRangeExist(NR As String) As Boolean
Dim checker As Range
On Error Resume Next
Set checker = Range(NR)
On Error GoTo 0
If checker Is Nothing Then
    DoesNamedRangeExist = False
Else
    DoesNamedRangeExist = True
End If
End Function

Sub ClearAllNamedRanges()
Dim NR
For Each NR In ActiveWorkbook.Names
    NR.Delete
Next
End Sub

Код события для выбора диапазонов (это относится к используемому вами листу - в моем случае Sheet1):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("E2:E4")) Is Nothing Then Range(Target.Value).Select

End Sub

Результаты Когда я нажимаю Ливерпуль (в ячейке E3).

Нажмите

Результаты диспетчера имен:

Результаты диспетчера имен

person user1274820    schedule 26.10.2015
comment
Большое спасибо за ответ! Я постараюсь понять, что вы написали. - person Helen; 26.10.2015
comment
Нет проблем :) Если у вас несколько рабочих листов, вы можете указать имена листов (вместо использования Range("B2:B10") вы хотели бы использовать Sheets("Sheet1").Range("B2:B10") или что-то в этом роде. - person user1274820; 26.10.2015
comment
Благодарю вас! У меня возникла проблема со строкой c.Offset(0, -1).Name = c.Value. Какая-то недопустимая ошибка имени (у меня нет английского Excel). Он отлично работал с моим примером документа (ливерпуль, лондон и т. д.), но при переходе к реальным данным эта ошибка останавливает все. Есть идеи? - person Helen; 26.10.2015
comment
В ваших именах есть специальные символы? Именованные диапазоны не могут содержать специальные символы. - person user1274820; 26.10.2015
comment
Кроме того, убедитесь, что вы не читаете столбец A, потому что получение отрицательного смещения столбца от него приведет к поиску столбца перед A, который не существует. excelforum.com/excel-general/ - person user1274820; 26.10.2015
comment
Ах, вы правы, у меня были специальные символы! Благодарю вас! - person Helen; 27.10.2015