Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1 2 След.
RSS
Улучшенный ВПР: поиск в нескольких книгах, нужен хороший макрос
 
#1
28.06.2015 16:37:22
ДД, дорогие форумчане
Не люблю, обращаться за помощью к жителям форума, но так как у меня нет, абсолютно никаких навыков в vb, пришлось... выложенные ранее макросы не подходили под мое описание или я не понял, как их применить в моем случае.

Задача состоит в том, что необходимо заполнять/обновлять определенный файл (назовем его Основной или моя База данных) еженедельно из 18 файлов.
В моей БД около 70 столбцов и 14 вкладок разделяющих их по региональному признаку. Из всех книг (18 файлов) необходимо заносить обновленную информацию в эту БД в каждый лист по 3 столбца к примеру. Конечно можно делать это обычным ВПРом, но это представьте только, склеивать все листы и потом еще делать вручную ВПР с каждой книгой, это просто ужс, а еще вся проблема в том, что после разделения по отдельным листам, то что внизу каждого листа стоят какие-то непонятные мне итоги, условия и тд., которые делал другой человек, который уже уволился, в общем тихий ужас. Лучше оставить, все как есть и делать на уже отдельных вкладках с рабочими внизу итогами. Пробовал делать запись макроса на первом листе, когда делал вручную ВПР функцию, а потом копировать макрос и менять название листа и название файла откуда брать ту или иную инф. на другие, но возникла новая проблема, что необходим цикл воспроизведения функции ВПР, т.к. в каждой вкладке/листе (т.е. регионе) количество позиций разное, где-то 1000 строк, где-то 2000 строк. Нужно сначала посчитать количество уникальных значений в первом столбце к примеру и потом делать функцию ВПР столько раз, сколько этих строк. Дальше я по аналогии думаю смогу сделать для других книг и листов. Уфф незнаю, на сколько доступно объяснил.
 
 
 
#2
28.06.2015 21:13:09
Уважаемый yhobby!
Полагаю, такое ТЗ - "ПОДИ ТУДА — НЕ ЗНАЮ, КУДА, ПРИНЕСИ ТО — НЕ ЗНАЮ, ЧТО".  :D
Где структуры файлов-источников?
Одинаковы ли они?
Как Ваши 14 "региональных вкладок" завязаны с 18-ю файлами-источниками?
И т.д и т.п. ...
 
 
 
#3
28.06.2015 21:50:48
Мотя, мне нужно, чтобы количество жильцов и их адреса отображались в основном файле из вспомогательного.
 
 
 
#4
28.06.2015 21:56:12
Так тоже самое только укажите на файл
Код
=ВПР(A2;'C:\Documents and Settings\Администратор\Мои документы\Downloads\[Вспомогательный файл откуда берутся данные.xlsx]Общий'!$A$2:$C$51;2;ЛОЖЬ)
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.
 
 
 
#5
28.06.2015 22:00:40
CAHO, если вы не заметили в основном файле, количество уникальных значений в разных листах/вкладках разное и оно будет современем меняться и это в примере этих вкладок четыре и из одного файла, а в реальном примере, 18 вкладок и 20 файл вспомогательных

И данные заполняются только в два столбца, а в жизни там столбцов 70, поэтому нужен макрос, чтобы файл не весил много и автоматизировать его
Изменено: yhobby - 28.06.2015 22:02:32
 
 
 
#6
28.06.2015 22:19:02
Блин, как тяжко объяснять суть =\

Нужен макрос, потому что в реальности в основном файле вкладок 18 штук и в каждой вкладке в первом столбце разное количество уникальных значений (и оно меняется, раз в неделю). Количество столбцов и вообще форма в основном файле во всех вкладках одинаковая и не меняется, только количество строк разная. Сами итоги нах-ся внизу, но считаются не под первым столбцом, а под другими.
Всего столбцов 70. Вспомогательных файлов 20, они все с разной формой, длиной, но у них вся инф. находится на одном листе и информацию нужно заносить согласно уникальных значений, т.е. все просто, есть уникальное значение и напротив него, есть данные, которые нужно занести в основной
файл.
Руками очень тяжко, делать из всех этих файлов ВПР
 
 
 
#7
28.06.2015 22:22:07
Кажись понял.
Откройте основной и вспомогательный файл. И в основном файле нажмите ПОЕХАЛИ.
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.
 
 
 
#8
28.06.2015 22:26:48
b = Workbooks("Вспомогательный файл откуда берутся данные.xlsx").Worksheets(1).UsedRange.Row + Workbooks("Вспомогательный файл откуда берутся данные.xlsx").Worksheets(1).UsedRange.Rows.Count - 1

Ошибка в этой строчке Run Time Error 9 . Может проблема в том, что файлы лежат в другом месте?
 
 
 
#9
28.06.2015 22:27:37
Стоп!
В двух темах одинаковые по размеру файлы, одинаковы последние сообщени автора, в названии фигурирует одна и та же ВПР.
yhobb, прошу объяснить, почему создано две темы.
 
 
 
#10
28.06.2015 22:32:05
Цитата
yhobby написал:
Ошибка в этой строчке Run Time Error 9 . Может проблема в том, что файлы лежат в другом месте?
Закройте все файлы Excel. И откройте из одной папки только эти два файла заново. И нажмите ПОЕХАЛИ.
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.
 
 
 
#11
28.06.2015 22:32:17
vikttur,эту тему я создал первой, извиняюсь. Тут я расписал ее, но походу перемудрил, никто ничего не писал. После нескольких раздумий решил попросить помощи, немного изменить вопрос, как создать цикл, а не полное решение проблемы, а потом все закрутилось и одновременно в двух темах, получилось дублирование, в общем полный провал =ъ

CAHO, вроде ОНО!))) ща я пока не хочу торопиться с выводами, посмотрю, как работает на оригинале. Сразу не понял, что нужно вспомогательный открывать


vikttur, я извиняюсь, что создал путоницу, ща проверю, помогло решение САНО и отвечу, а пока незнаю, какую закрывать из них =\
 
 
 
#12
28.06.2015 22:39:03
Ответил ТУТ
Согласие есть продукт при полном непротивлении сторон
 
 
 
#13
28.06.2015 22:39:18
yhobby, создание тем-дублей - нарушение правил форума.

Автору 20% штрафа (при 100% - автоматический бан)
 
 
 
#14
28.06.2015 22:40:47
Витя, я ту тему закрыл. Нефиг тут разводить демагогию и прочее. Дубль темы по адресу: http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=67390&TITLE_SEO=67390-kak-nastro...
Даже самый простой вопрос можно превратить в огромную проблему. Достаточно не уметь формулировать вопросы...
 
 
 
#15
28.06.2015 22:46:21
Sanja, Спасибо большое, да я видел ваш ответ в другой теме, еще не проверял. Я плохо в макросе разбераюсь, сейчас сижу разбираюсь с тем, что САНО написал
vikttur, да, я понимаю. Я смотрю вы оставили уже эту)
CAHO, не могли бы написать разъяснения по вашему макросу (что делает каждая строчка), т.к. в основном файле есть 18 вкладок в которые нужно вносить файлы и еще одна вкладка, куда уходят итоги со всех листов (т.е туда ненадо).
 
 
 
#16
28.06.2015 22:56:05
Код
Sub Макрос1()
'Определяем номер последней заполненной строки в спомогательном файе
b = Workbooks("Вспомогательный файл откуда берутся данные.xlsx").Worksheets(1).UsedRange.Row + Workbooks("Вспомогательный файл откуда берутся данные.xlsx").Worksheets(1).UsedRange.Rows.Count - 1
'Перебираем все листы в основном файле
For Each Shet In ActiveWorkbook.Sheets
'Начинаем со второй строки каждого листа и до последней строки листа
    For a = 2 To Sheets(Shet.Name).UsedRange.Row + Sheets(Shet.Name).UsedRange.Rows.Count - 1
'Вставляем формулу ВПР во второй и третий столбец
    Sheets(Shet.Name).Cells(a, 2).FormulaR1C1 = "=VLOOKUP(RC[-1],'[Вспомогательный файл откуда берутся данные.xlsx]Общий'!R2C1:R" & b & "C3,2,FALSE)"
    Sheets(Shet.Name).Cells(a, 3).FormulaR1C1 = "=VLOOKUP(RC[-2],'[Вспомогательный файл откуда берутся данные.xlsx]Общий'!R2C1:R" & b & "C3,3,FALSE)"
    Next a
Next Shet
End Sub
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.
 
 
 
#17
28.06.2015 22:59:12
CAHO,а если мне нужно заполнять данные в определнные листы в основном файле?

Можете в Ваш пример добавить еще один вспомогательный файл для примера (чисто гипотетически :), для моего понимания. Как я буду добавлять в реальном примере еще 20 файлов, для заполнения всех 70 столбцов

P.S. Данный макрос классно работает, только он немного другой. Нужно в определенный столбец вставлять и вкладку, я чет пока не понял как и где он определяет, куда и как =( такое ощущение, что в весь файл, по уникальному значению)
Изменено: yhobby - 28.06.2015 23:11:53
 
 
 
#18
30.06.2015 11:27:10
For a = 2 To Sheets(Shet.Name).UsedRange.Row + Sheets(Shet.Name).UsedRange.Rows.Count - 1

Подскажите почему выдает ошибку на букву "а" expected function or variable на этой строчке, весь скрипт выше. В оригинальном файле (отличается от примеров) 70 столбцов и несколько тысяч строк и вкладок 15 штук. Этот макрос работает на книгах примера, а на оригинале выдает на этой строчке ошибку, менял уже на всякие разные цифры))) что означает эта строчка?
Изменено: yhobby - 30.06.2015 11:27:44
 
 
 
#19
30.06.2015 11:41:42
yhobby, добрый день. В вспомогательных файлах строк очень много? Значения точно уникальны? Если просто собирать все 18 файлов в 1 и потом из него проставлять значения?
Учусь программировать :)
 
 
 
#20
30.06.2015 12:07:48
Проверьте на оригинале нет ли MISSING в референсах VBA.
 
 
 
#21
30.06.2015 13:13:59
Smiley,
- Все файлы собирать в один и потом из него уже что-то лепить, то слишком большой файл получится)))
- В вспомогательных файлах строк около 4000
- Значения всегда есть уникальные, но в вспомогательном файле они могут быть, не в  самом первом столбце Hugo, MISSING в референсах VBA... что это?!) я думаю, все дело было в том, что в оригинале основной файл в формате xlsb,  а вспомогательные в xlsx и они не поддерживают макрос? Сейчас занимаюсь проверкой, вроде по чуть, чуть со скриптом разобрался, немного меняю его под себя.

ОДИН вопрос! Как сделать, чтобы цикл функции ВПР заканчивался, после окончания уникальных значений или после появления внизу пустых строк, а то у меня спустя 3-4 строки идут уже итоги, помогите плс)))
 
 
 
#22
30.06.2015 13:52:44
Цитата
yhobby написал:
Значения всегда есть уникальные, но в вспомогательном файле они могут быть, не в  самом первом столбце
А как это понимать? :)
Учусь программировать :)
 
 
 
#23
30.06.2015 13:58:44
Smiley, ай ладно)) в этом я уже разобрался

Как сделать, чтобы цикл функции ВПР заканчивался, после окончания уникальных значений или после появления внизу пустых строк, а то у меня спустя 3-4 строки идут уже итоги, помогите плс)))

И еще сейчас все работает, кроме вопроса чуть выше конечно, но почему-то все происходит каааапееец медленно может что-то с циклом? он смотрит лишнюю информацию? я запускаю только с одного файла и только заполнить один столбец и на это уходит у моего компьютера минут 15. Обычным ВПРом и то быстрее было бы в миллион раз))) help help help
 
 
 
#24
30.06.2015 14:20:51
Цитата
yhobby написал:
а то у меня спустя 3-4 строки идут уже итоги, помогите плс)))
Вычтите эти 3 строки если они одинаковые
Код
(Sheets(Shet.Name).UsedRange.Row + Sheets(Shet.Name).UsedRange.Rows.Count - 1)-3
Цитата
уходит у моего компьютера минут 15
Попробуйте использовать в начале и конце макроса следующую структуру
Код
Sub Траливали
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
.......

Тут код макроса
.......
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Мастерство программиста не в том, чтобы писать программы, работающие без ошибок.
А в том, чтобы писать программы, работающие при любом количестве ошибок.
 
 
 
#25
30.06.2015 14:23:29
CAHO,спасибо большое за поддержку, сейчас попробую. Так как я новичок в этом деле, может занять время)
 
 
 
#26
30.06.2015 14:33:37
CAHO,строки кстати не одинаковые. Они разделены просто строками которые залиты цветом для глаза. Под столбцом 40 считает суммарные итоги с столбца 25,26,27 к примеру. Под столбцом 50 сумму значений своего столбца. Под этими итогами еще всякие примечания, что означает, какой цвет))) короче всякая всячина внизу. А уникальные значения в первом столбце уже давно закончились к примеру на строке 1000, а внизу есть еще в перемешку всякие записи и примечания под разными столбцами))) Как это учесть и сделать, чтобы он подсчитал кол-во строк только первого столбца и выполнял vlookup столько, сколько строк в первом столбце?
 
 
 
#27
30.06.2015 14:36:48
CAHO, Оо помогло)))) круть отработал один столбец в 17 вкладках за 5 секунд))) (пока с одного файла) Ура ура ура
 
 
 
#28
30.06.2015 15:04:32
Как сделать, чтобы у меня в основном файле значения подставлялись не форумалами, а значениями! А то суть всего макроса теряется, спасибо заранее)
 
 
 
#29
30.06.2015 15:06:48
Цитата
yhobby написал: значения подставлялись не форумалами, а значениями
Код
Range.Value=Range.Value

Формула массива (ФМ) вводится Ctrl+Shift+Enter
Memento mori
 
 
 
#30
30.06.2015 15:09:08
Чтобы ничего не переделывать, добавьте в конце следующие строки:
Код
With ActiveSheet.UsedRange
    .Value = .Value
End With
 
 
 
Страницы: 1 2 След.
Читают тему
Наверх