Доброго времени суток всем всем всем!!!, в данной теме хотел бы раскрыть ВПР по средствам VBA, давно обсуждаемая тема конечно, но есть несколько нюансов 1.По запуску макроса необходимо выбрать несколько книг в диалоговом окне, во всех этих книгах есть КЛЮЧЕВОЕ поле по которому производится ВПР. 2.После выбора каждая книга открывается по очереди, для выбора диапазона необходимых данных и так же указывается номер таблицы (все указывается через InputBox) после чего книга закрывается и открывается следующая. 3.Данные должны вставать друг за другом, то есть допустим у нас 3 книги - это три столбца, а не один. Так же хотелось чтобы можно было осуществлять ВПР по двум критериям, но это уже просто СУПЕР БУДЕТ
1 и 2 пункт в принципе реализованы, 3 не понимаю как сделать да и сам ВПР почему то сломался, думаю там адрес диапазона съезжает.
Доброго времени суток всем всем всем!!!, в данной теме хотел бы раскрыть ВПР по средствам VBA, давно обсуждаемая тема конечно, но есть несколько нюансов 1.По запуску макроса необходимо выбрать несколько книг в диалоговом окне, во всех этих книгах есть КЛЮЧЕВОЕ поле по которому производится ВПР. 2.После выбора каждая книга открывается по очереди, для выбора диапазона необходимых данных и так же указывается номер таблицы (все указывается через InputBox) после чего книга закрывается и открывается следующая. 3.Данные должны вставать друг за другом, то есть допустим у нас 3 книги - это три столбца, а не один. Так же хотелось чтобы можно было осуществлять ВПР по двум критериям, но это уже просто СУПЕР БУДЕТ
1 и 2 пункт в принципе реализованы, 3 не понимаю как сделать да и сам ВПР почему то сломался, думаю там адрес диапазона съезжает. Elhust
Для начала, тогда, что эта тема делает в ветке "Вопросы по Excel", если есть ветка "Вопросы по VBA"?
Скачай вложение там лежит Excel файл , и Макрос - это макрокоманда(так звучит в правильном переводе с англ.языка), содержащая последовательность действий, записанных пользователем. Запись производится с помощью встроенного в пакет Microsoft Office языка программирования - Visual Basic for Application(VBA). [moder]Перенес тему в вопросы по VBA[/moder]
Для начала, тогда, что эта тема делает в ветке "Вопросы по Excel", если есть ветка "Вопросы по VBA"?
Скачай вложение там лежит Excel файл , и Макрос - это макрокоманда(так звучит в правильном переводе с англ.языка), содержащая последовательность действий, записанных пользователем. Запись производится с помощью встроенного в пакет Microsoft Office языка программирования - Visual Basic for Application(VBA). [moder]Перенес тему в вопросы по VBA[/moder]Elhust
Каждый сам выбирает правила игры
Сообщение отредактировал SLAVICK - Вторник, 11.10.2016, 14:43
Public Sub test() Dim lf As Long Dim iRange1 As Object, iRange2 As Range Dim NamTable As Integer, i As Long, ILastRow As Long, iFiles Dim objWorkbook As Excel.Workbook Dim j As Integer 'Dim StartTime As Variant
Set objWorkbook = ThisWorkbook
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True .Title = "Выбрать файлы" 'заголовок окна диалога .Filters.Clear 'очищаем установленные ранее типы файлов .Filters.Add "Excel files", "*.xls*;*.xlsx", 1 'устанавливаем возможность выбора только файлов Excel .Filters.Add "Text files", "*.txt", 2 'добавляем возможность выбора текстовых файлов .FilterIndex = 1 'устанавливаем тип файлов по умолчанию .InitialFileName = ThisWorkbook.Path & "\" 'назначаем папку отображения и имя файла по умолчанию .InitialView = msoFileDialogViewList 'вид диалогового окна(доступно 9 вариантов)
If .Show = 0 Then Exit Sub 'показывает диалог 'цикл по коллекции выбранных в диалоге файлов j = 0 For lf = 1 To .SelectedItems.Count Workbooks.Open .SelectedItems(lf) Debug.Print .SelectedItems(lf)
Set iRange2 = Application.InputBox("Выберите диапозон", "Второе значение", Type:=8) NamTable = Application.InputBox("Введите номер столбца для отбора данных", "Третье значение", Type:=1)
For i = 1 To 42 objWorkbook.Worksheets(1).Cells(i + 1, 2 + j) = Application.VLookup(objWorkbook.Worksheets("Лист1").Cells(i + 1, 1).Value, iRange2, NamTable, False) Next i j = j + 1 Workbooks(ActiveWorkbook.Name).Close Next End With End Sub
[/vba]
Насколько понял [vba]
Код
Option Explicit
Public Sub test() Dim lf As Long Dim iRange1 As Object, iRange2 As Range Dim NamTable As Integer, i As Long, ILastRow As Long, iFiles Dim objWorkbook As Excel.Workbook Dim j As Integer 'Dim StartTime As Variant
Set objWorkbook = ThisWorkbook
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True .Title = "Выбрать файлы" 'заголовок окна диалога .Filters.Clear 'очищаем установленные ранее типы файлов .Filters.Add "Excel files", "*.xls*;*.xlsx", 1 'устанавливаем возможность выбора только файлов Excel .Filters.Add "Text files", "*.txt", 2 'добавляем возможность выбора текстовых файлов .FilterIndex = 1 'устанавливаем тип файлов по умолчанию .InitialFileName = ThisWorkbook.Path & "\" 'назначаем папку отображения и имя файла по умолчанию .InitialView = msoFileDialogViewList 'вид диалогового окна(доступно 9 вариантов)
If .Show = 0 Then Exit Sub 'показывает диалог 'цикл по коллекции выбранных в диалоге файлов j = 0 For lf = 1 To .SelectedItems.Count Workbooks.Open .SelectedItems(lf) Debug.Print .SelectedItems(lf)
Set iRange2 = Application.InputBox("Выберите диапозон", "Второе значение", Type:=8) NamTable = Application.InputBox("Введите номер столбца для отбора данных", "Третье значение", Type:=1)
For i = 1 To 42 objWorkbook.Worksheets(1).Cells(i + 1, 2 + j) = Application.VLookup(objWorkbook.Worksheets("Лист1").Cells(i + 1, 1).Value, iRange2, NamTable, False) Next i j = j + 1 Workbooks(ActiveWorkbook.Name).Close Next End With End Sub
Ларчик то просто открывался Благодарю! и думаю пока тему можно не закрывать, хочу ещё подумать по поводу доработки. - Сделать возможность ВПР по двум критериям.(Так чтобы можно было выбрать по двум или по одному из) - И ещё возможность добавлять не один столбик а несколько тоже на выбор при условии что они находятся в разных местах
Ларчик то просто открывался Благодарю! и думаю пока тему можно не закрывать, хочу ещё подумать по поводу доработки. - Сделать возможность ВПР по двум критериям.(Так чтобы можно было выбрать по двум или по одному из) - И ещё возможность добавлять не один столбик а несколько тоже на выбор при условии что они находятся в разных местахElhust
Каждый сам выбирает правила игры
Сообщение отредактировал Elhust - Среда, 12.10.2016, 10:16
Думаю можно с помощью MsgBox в цикл по книгам добавить один для двух критериев другой для возможности выбора и добавления нескольких столбиков или проще форму сделать , как рациональней можно выполнить задачу ?
Думаю можно с помощью MsgBox в цикл по книгам добавить один для двух критериев другой для возможности выбора и добавления нескольких столбиков или проще форму сделать , как рациональней можно выполнить задачу ?Elhust
отбор по двум критериям больше (подходит к теме "Вопросы Ексель"): способ работает, если в источнике данных можно добавить "технический" столбец, содержимое ячейки = критерий1 & критерий2. в стандартой функции впр задается условие "критерий1 & критерий2". Работает достаточно быстро.
отбор по двум критериям больше (подходит к теме "Вопросы Ексель"): способ работает, если в источнике данных можно добавить "технический" столбец, содержимое ячейки = критерий1 & критерий2. в стандартой функции впр задается условие "критерий1 & критерий2". Работает достаточно быстро.DOK2014
Чет я туплю сегодня... Попробовал и с файлом Elhust, и с файлом Udik, - везде во втором столбце получаю "красиво"... Короче, вам "шашечки или ехать"? Обязательно ВПР? В VBA это решается примерно так: 1 берем данные в массив 2 проходим по искомому столбцу 3 если совпадает - записываем 4 если есть второй критерий - проверяем на совпадение в соответствующем столбце 5 третий критерий - тоже самое 6 и т.д.
Чет я туплю сегодня... Попробовал и с файлом Elhust, и с файлом Udik, - везде во втором столбце получаю "красиво"... Короче, вам "шашечки или ехать"? Обязательно ВПР? В VBA это решается примерно так: 1 берем данные в массив 2 проходим по искомому столбцу 3 если совпадает - записываем 4 если есть второй критерий - проверяем на совпадение в соответствующем столбце 5 третий критерий - тоже самое 6 и т.д.Michael_S