Форум программистов «Весельчак У»
  *
Добро пожаловать, Гость. Пожалуйста, войдите или зарегистрируйтесь.
Вам не пришло письмо с кодом активации?

  • Рекомендуем проверить настройки временной зоны в вашем профиле (страница "Внешний вид форума", пункт "Часовой пояс:").
  • У нас больше нет рассылок. Если вам приходят письма от наших бывших рассылок mail.ru и subscribe.ru, то знайте, что это не мы рассылаем.
   Начало  
Наши сайты
Помощь Поиск Календарь Почта Войти Регистрация  
 
Страниц: [1]   Вниз
  Печать  
Автор Тема: Формулы диапазона в Эксель через VBA  (Прочитано 1805 раз)
0 Пользователей и 1 Гость смотрят эту тему.
..::SCRIBE::..
Помогающий

ua
Offline Offline
Пол: Мужской
не путайте банальность с ленью=)


« : 19-10-2016 13:34 » 

Привет всем.

Начальные условия:
1.Есть средний диапазон данных (5 тыс. строк).
2.Нужно подтянуть к этим данным информацию из другой книги.

Примечание:
В скрипте это все запускается через отдельный инстанс Экселя.
Все ускорения произведены, Visible, Alerts, Updating, Events etc.
Первая книга имеет формат .xls, другая .csv (ну т.е. при открытии она ж все равно автоматом создается)

Попытки решить:
1. В лоб, в цикле подтянуть. Как оказалось, это не шибко быстро, хотя, ВООБЩЕ не быстро.  Не могу...
2. Ручная попытка показала что формулы Эксель (а именно ВПР), работает в разы быстрее, и было решено это дело автоматизировать путем создания оных через скрипт.

То, что хотелось бы видеть рабочим (попытка переделать советы stackoverflow):
Код: (Visual Basic)
Set xlApp = CreateObject("Excel.Application")
Set wbFact = xlApp.Workbooks.Open(fFact)
Set wsFact = wbFact.Worksheets(1)
Set wbClients = xlApp.Workbooks.Open(fClients)
Set wsClients = wbClients.Worksheets(1)
factMaxRow = wsFact.UsedRange.Rows.Count + wsFact.UsedRange.Row - 1
clientsMaxRow = wsClients.UsedRange.Rows.Count + wsClients.UsedRange.Row - 1
Set clRngA = wsClients.Range(wsClients.Cells(2, 1), wsClients.Cells(clientsMaxRow, 12))
Set clRngB = wsClients.Range(wsClients.Cells(2, 2), wsClients.Cells(clientsMaxRow, 12))
Set clRngC = wsClients.Range(wsClients.Cells(2, 3), wsClients.Cells(clientsMaxRow, 12))
factCol = 7
Set factRange = wsFact.Range(wsFact.Cells(2, factCol), wsFact.Cells(factMaxRow, factCol))
factRange.Formula = "=IF(IFERROR(VLOOKUP(RC1;" & wbClients.Name & "!" & clRngA.Address & ";" & CStr(factCol) & ";FALSE))=TRUE;IF(IFERROR(VLOOKUP(RC1;" & wbClients.Name & "!" & clRngB.Address & ";" & CStr(factCol - 1) & ";FALSE))=TRUE;VLOOKUP(RC1;" & wbClients.Name & "!" & clRngC.Address & ";" & CStr(factCol - 2) & ";FALSE);VLOOKUP(RC1;" & wbClients.Name & "!" & clRngB.Address & ";" & CStr(factCol - 1) & ";FALSE));VLOOKUP(RC1;" & wbClients.Name & "!" & clRngA.Address & ";" & CStr(factCol) & ";FALSE))"

Смысл формулы - во чтобы то ни стало подтянуть данные (есть просто 3 идентифицирующие колонки в таблице)

В общем, ошибка "1004". Формулы попроще работают, эта фигня не хочет, прошу указать на ошибку. Как я не пробовал только указывать и ячейки, и диапазоны...

Спасибо.
« Последнее редактирование: 19-10-2016 13:36 от ..::SCRIBE::.. » Записан
HandKot
Молодой специалист

ru
Offline Offline

« Ответ #1 : 20-10-2016 07:22 » 

..::SCRIBE::..
1) для начала попробуйте использовать не factRange.Formula, а factRange.FormulaRC
2) потом, тут нашёл аддон http://analystcave.com/excel-tools/excel-sql-add-in-free/, посмотрите, может сможете использовать
 3) и для обновления я бы попытался использовать запросы, но пока проверить не могу

ЗЫЖ видать из-за прокси форум чудит и я не могу нормально ответить
Записан

I Have Nine Lives You Have One Only
THINK!
..::SCRIBE::..
Помогающий

ua
Offline Offline
Пол: Мужской
не путайте банальность с ленью=)


« Ответ #2 : 20-10-2016 07:59 » 

Для factRange.FormulaRC ошибка "438":"Object doesn't support this method"
Ну собственно реально такого метода нет, есть FormulaR1C1 и FormulaR1C1Local, но при нем все та же ошибка.
Записан
..::SCRIBE::..
Помогающий

ua
Offline Offline
Пол: Мужской
не путайте банальность с ленью=)


« Ответ #3 : 20-10-2016 08:22 » new

Как я вижу, ошибка в clRngA.Address, или RC1, т.е. надо выбрать что-то одного, или Formula или FormulaR1C1, в формуле просто вперемешку.

Как и предполагалось, решение:
1. Ячейки текущей книги и формулы задавать стандартным способом (не RC)
2. Диапазон второй кники оставить как есть.
3. Использовать метод FormulaLocal (у кого не русская версия, я не виноват  Не могу...)
4. Использовать русские название формул.

Почему я так решил? Потому что если задать английскими, результат ВПР(VLOOKUP) то ли конвертируется в русскую кодовою страницу, то ли еще что, на выходе кракозябры, с FormulaLocal такого нет.

В конце, тестовая формула:
factRange.FormulaLocal = "=ВПР($A$1;" & wbClients.Name & "!" & clRngA.Address & ";7;ЛОЖЬ)"
« Последнее редактирование: 20-10-2016 08:49 от ..::SCRIBE::.. » Записан
Страниц: [1]   Вверх
  Печать  
 

Powered by SMF 1.1.21 | SMF © 2015, Simple Machines