Есть небольшое изменение на сайте НБУ (изменен протокол передачи данных с http на https). Если у Вас приобретенная версия Plex и есть определенные навыки VBA (хотя бы самые маленькие, как у меня), то найдите в коде текст "sURI = "http://bank.gov.ua/NBUStatService/v1...." и измените http на https.
Спасибо, теперь с диапазоном проблем нет. Но в остальном...Если перефразировать одного героя - "Знания хромают. Они есть, только хромают". В данном случае на обе ноги хромают. Переделал код, т.к. в результате не получил желаемого. Ниже приведу часть кода Что хотел получить в данной части кода: в ячейку G2 (лист OSNOVA, книга rab) вставляются данные из книги baza (нужный лист выбирается другой частью кода). Данные должны соответствовать сцепке "Данные"+"код" В результате творения строки просто сдвигаются на одну позицию вниз.
Код
With sht
a = .Range(.Cells(4, 1), .Cells(22, 5)).Value
End With
Set dic = CreateObject("scripting.dictionary")
For i = 4 To UBound(a)
s = a(i, 2) & "|" & a(i, 3)
dic(s) = a(i, 4)
Next
With sht
a = rab.Sheets("OSNOVA").[a1].CurrentRegion.Value
End With
For i = 2 To UBound(a)
s = a(i, 2) & "|" & a(i, 5)
a(i, 8) = dic(s)
Next
rab.Sheets("OSNOVA").[a1].Offset(1).Resize(UBound(a), 8).Value = a
Спасибо за совет, в рабочем файле я использую отключение обновления дисплея (DisplayAlerts), но не очень помогает. Из последнего, что не получается (прошу сильно не ругать за ошибки, я к VBA на "Вы" , т.е. знаний мало), как описывал раньше за основу взял код Hugo121 (спасибо) с другого сайта.
Код
Sub Primer_2()
Dim rab, baza As Workbook
Dim sht As Worksheet
Dim rRange, rCell As Range
Dim i&, a()
Set rab = ActiveWorkbook
LastOSNOVA = Cells(Rows.Count, 1).End(xlUp).Row
Set baza = Workbooks("baza.xlsm")
For Each sht In baza.Worksheets
If sht.Range("A1").Value = rab.Sheets("OSNOVA").Range("I1").Value Then
sht.Activate
NewName = sht.Name
BookName = ActiveWorkbook.Name
Lastbaza = Cells(Rows.Count, 1).End(xlUp).Row
With CreateObject("scripting.dictionary")
a = baza.NewName.Range(Cells(4, 1), Cells(22, 5)).Value
For i = 2 To UBound(a): .Item(a(i, 2) & "|" & a(i, 3)) = a(i, 3): Next
a = rab.Sheets("OSNOVA").[a1].CurrentRegion.Value
For i = 2 To UBound(a): a(i, 1) = .Item(a(i, 2) & "|" & a(i, 5)): Next
rab.Sheets("OSNOVA").Cells(2, 7).Resize(UBound(a), 1) = a
End With
End If
Next
End Sub
На данном этапе выдает ошибку в этой строке (пробовал как в оригинале кода - a = baza.NewName.[a1].CurrentRegion.Value, но тоже не получается):
Код
a = baza.NewName.Range(Cells(4, 1), Cells(22, 5)).Value
Добрый день! Пытаюсь как-то оптимизировать свою работу. Есть две книги (упрощенные версии вложил) rab, baza. В книгу rab с помощью ВПР (вставляю макросом) подтягиваются данные из книги baza. Все работает, но когда в книге rab много строк (несколько тысяч), то работа заметно замедляется. Как-то мне помогли на планете в подобной ситуации через словари (scripting.dictionary). Пробовал по аналогии, не получается, нашел на дружественном ресурсе описание Dictionary и Collection, но пока не получается разобраться. Код по которому получаю данные:
Код
Sub Primer()
Dim rab, baza As Workbook
Dim sht As Worksheet
Dim rRange, rCell As Range
Set rab = ActiveWorkbook
LastOSNOVA = Cells(Rows.Count, 1).End(xlUp).Row
Set baza = Workbooks.Open("baza.xlsm")
For Each sht In baza.Worksheets
If sht.Range("A1").Value = rab.Sheets("OSNOVA").Range("I1").Value Then
sht.Activate
NewName = sht.Name
BookName = ActiveWorkbook.Name
Lastbaza = Cells(Rows.Count, 1).End(xlUp).Row
rab.Sheets("OSNOVA").Range("G2").FormulaLocal = _
"=ВПР(СЦЕПИТЬ(B2;F2);" & Workbooks(BookName).Sheets(NewName).Range("A:G").Address(External:=True) & ";4;0)"
rab.Sheets("OSNOVA").Range("H2").FormulaLocal = _
"=ВПР(СЦЕПИТЬ(B2;F2);" & Workbooks(BookName).Sheets(NewName).Range("A:G").Address(External:=True) & ";5;0)"
rab.Activate
Sheets("OSNOVA").Range("G2:H2").AutoFill Destination:=Range(Cells(2, 7), Cells(LastOSNOVA, 8))
End If
Next
End Sub
Почему формула, а не значения (изначально я получал значения, как в первом посте) - все просто до безобразия. Коллеги, которые обрабатывают данные дальше (работают "этажом" выше) сказали надо формула и усе, руками или макросом их не волнует, формула и точка. Извините, за оффтоп. Еще раз спасибо за помощь и консультацию
Да, работает. Большое спасибо! Подскажите книгу или ресурс, где можно синтаксис подучить, где правильно пробел, а где кавычки ставить (в Уокенбаха вроде бы не нашел, хотя могу сильно ошибаться, т.к. смотрел давно и не очень внимательно)
В определенную ячейку вводятся данные по сцепке. А как вместо данных вставить формулу (=СЦЕПИТЬ())? Когда известны ячейки, то у меня получается, а вот когда переменные – нет. Вроде бы и тема часто встречается, но пока никак не получается решить вопрос. Прошу помочь разобраться. Спасибо!
Мотя, спасибо за Ваш вариант, но для моей задачи наиболее подходящий вариант решения, который предложил ктулху. Как уже писал выше, код предложеный ктулху адаптировал под рабочий файл. Все работает достаточно быстро. Спасибо всем, кто откликнулся на помощь! Спасибо, ктулху, за решение вопроса!
Михаил, задача - найти и поставить в каждую ячейку стоблца "Результат" файла "Проба" минимальное значение по трем критериям из файла "Итог". Например, в ячейке D2 на листе1 книги "Проба" минимальное значение для соотношения критериев
форма 1
цвет 2
содержание 4
=2. ктулху, спасибо! Уже вставил код в рабочий файл, все работает на много быстрее.
Здравствуйте! Прошу помочь разобраться с кодом.В каждой ячейке столбца "Результат" (файл "Проба" ) стояла формула массива, которая брала минимальное значение из файла "Итог" , соответствующее определенным критериям. '{=МИН(ЕСЛИ(($A2=Итог.xls!$A$2:$A$23)*($B2=Итог.xls!$B$2:$B$23)*($C2=Итог.xls!$C$2:$C$23)*(Итог.xls!$D$2:$D$23);Итог.xls!$D$2:$D$23))} Записал формулу макрорекодером и добавил прохождение циклом по всем ячейкам столбца "Результат".
Код
Sub proba()
Dim Str, i As Integer
StrokaLast = Cells(Rows.Count, 1).End(xlUp).Row
With Application: .ScreenUpdating = False: .DisplayAlerts = False: .EnableEvents = False: End With
i = 2
For i = 2 To StrokaLast
Cells(i, 4).FormulaArray = _
"=MIN(IF((RC1=Итог.xls!R2C1:R23C1)*(RC2=Итог.xls!R2C2:R23C2)*(RC3=Итог.xls!R2C3:R23C3)*(Итог.xls!R2C4:R23C4),Итог.xls!R2C4:R23C4))"
Cells(i, 4).Value = Cells(i, 4).Value
Next
With Application: .ScreenUpdating = True: .DisplayAlerts = True: .EnableEvents = True: End With
End Sub
Макрос работает, но когда много строк (в книге "Проба" несколько тысяч, а в книге "Итог" несколько десятков тысяч), то сильно тормозит. Подскажите, пожалуйста, как можно усовершенствовать (или изменить) код, что бы быстрее работал, как можно изменить саму формулу, если заранее не известно количество строк в файле "Итог" (я понимаю, что необходимо найти последнюю заполненную ячейку, но не знаю как корректно записать формулу)? Спасибо! ПС Файлы-примеры вложил.
Не знаю как корректно заменить имена разрешиловка.xlsx и Лист1 на Raz и sht (данные имена присваиваются в процессе работы кода) Возможно символ типа "&" или скобки нужны, не знаю пробовал и так и этак :(
Johny, спасибо, заменил, но опять же проблема с "не допустимым именем"
Здравствуйте! Прошу помочь разобраться с синтаксисом формулы, которую хочу вставить в ячейку с помощью кода VBA.
TD, AN - имена книги и листа, в которую вставляю формулу Raz, sht - имена книги и листа, с которых берутся данные (книга в сетевом доступе, на момент вставки формулы будет открыта) Пытаюсь прописать формулу ВПР в ячейку W2
TD.Sheets("AN").Range("W2").FormulaLocal = "=ВПР(С2, Raz.sht!AA:FF,3,0)" Пробовал через макрорекодер, но и в том виде не знаю как записывать корректно присвоенные имена листов и книг. Спасибо!
о третьему заданию. Что то не то. Я пересчитал в ручную, ваша цифра не правильна.
McCooper, Вы ошиблись. Посмотрите справку по ф-ции ДЕНЬНЕД, обратите внимание на тип недели Сравните какой результат по первой дате (01.08.2009) в примере выдаст формула, которая используется в варианте ZVI (ДЕНЬНЕД(B4;2) и результат формулы, которую используете Вы (ДЕНЬНЕД(B4).
У меня возникла проблема с сборкой листов из разных книг в текущую в PLEX 2.0 При выборе собирать "листы с именем" программа ругается ошибкой "9" на строку OB.Sheets(k).Copy after:=CB.Sheets(CB.Sheets.Count) или на строку oldname = OB.Sheets(k).Name (в зависимости от того выбрано или нет "присваивать собранным листам имена файлов" Пробовал собирать из разных книг, с разными именами листов - не работает, хотя предыдущая версия собирает листы при тех же заданных условиях.
Excel 2010, WIN 7, PLEX 2.0
ПС И, конечно же, еще раз спасибо за надстройку Николаю и всем, кто принимал участие в доработке PLEX 2.0! ПС 2 Хочу попросить тех, кто уже установил PLEX 2.0 проверить работает ли у них "Сборка листов из разных книг в текущую", если указать "листы с именем"? Спасибо.
- Ты че такой мрачный? - Да ты че не знаешь? Петрович умер... - Да ты че!!!! Как?... - Да пришел домой, выпил, лег на диван, закурил.. - Че, сгорел, что ли?!!!!! - Да не... Успел он окно открыть и в форточку прыгнуть... - Че разбился что ли?!!!!!!! - Да не... Он перед тем как прыгать успел пожарных вызвать, они тент успели натянуть, он спружинил неудачно и опять в окно влетел.... - Да епт, че все-таки сгорел?!!!!!!!! - Да не... Он за раму зацепился и обратно сиганул..... - Да мля!!!! Че разбился все таки что ли?!!!!!!! - Да не... Там же пожарники с тентом, он спружинил и на проезжую часть, а там камаз тентованный шел.. он отскочил от него и обратно в окно... - ДА ЕПТ!!! КАК ПОГИБ ТО!!! - Да... Пристрелили его - задолбал всех..
----------------------------------------------------------- Воспитанный мужчина не сделает замечания женщине, плохо несущей шпалу.
Alex_ST, спасибо. Получается, если у Вас после прохождения макросом пропали #Н/Д, а у меня, на том же примере, нет, то решение вопроса возможно в настройках форматов самой системы или настройках форматов Excel на моем комп. Буду размбираться.
Выкладываю пример, что-бы показать суть проблемы. В примере на лист2 с помощью ВПР подтягиваю "номер" с листа Sheet1. По некоторым позициям номер подтягивается, а по некоторым выдает ошибку Н/Д. Если в ячейках, где ВПР выдал ошибку нажать F2 и "энтер", ошибка пропадает. Было бы очень хорошо, если-бы код, который привел Alex_ST исправил ошибку, но, к сожалению, не исправляет . Сложить с "0", тоже не дает результат (можете попробывать), при замене "," на "," некотрые ошибки пропадают, но появляются другие.
Alex_ST пишет: ikki, по твоему предложению получилось всё супер-тупер карсиво и компактно:
Код
Sub Repair_Value()
Intersect(ActiveWindow.RangeSelection, ActiveSheet.UsedRange).SpecialCells(xlCellTypeLastCell).Offset(1).Copy
ActiveWindow.RangeSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd
End Sub
Спасибо за идею.
Здравствуйте. Извините, тема не моя, возможно не так понял суть. Попробовал использовать вариант Вашего (совместно с ikki ) примера. Насколько я понял, то выделив диапазон ячеек (формат текстовый), а потом выполнив указанный код, то формат в выделенных ячейках становится числовым (по аналогии если сложить с 0 через спецвставку). Но у меня после выполнения кода формат не меняется, остается текстовым. В чем может быть причина? Или я не разобрался с вопросом?
А ларчик просто открывался :) Большое Вам спасибо, The_Prist. Остался маленький вопрос. Листы сохраняются под именем книги из которой копируются (как и планировалось) + расширение (не планировалось) - Имя.xls, ЕщеИмя.xlsm. Как сохранять без расширения (Имя, ЕщеИмя)?
Здравствуйте. Понимаю, что тема « с оскоминой». Поиском пользовался, похожие темы читал. Сильно не ругайте. Из огромного множества решений данного вопроса на этом (и не только) форуме наиболее подходящее для себя нашел в приемах. Вот только в том коде файлы, из которых копируются листы, выбираются через диалоговое окно, в моем случае листы должны копироваться из открытых в данный момент книг. Слабая попытка реализовать решение через код: Sub CopList() Dim WB As Workbook Application.ScreenUpdating = False For Each WB In Workbooks If WB.Name <> ThisWorkbook.Name Then WB.Sheets(1).Copy After:=ThisWorkbook.Sheets (ThisWorkbook.Sheets.Count) ActiveSheet.Name = WB.Name End If Next WB Application.ScreenUpdating = True End Sub
Выдает ошибку: «1004» «Не удается вставить листы в конечную книгу, так как она содержит меньшее число строк и столбцов, чем исходная книга.» Прошу помочь разобраться. Спасибо.
Супер! Пару вопросов для образовательных целей 1. nerv, как в Вашем коде реализована сортировка? 2. что нужно изменить в коде что-бы выполнялся не ввод значения в ячейку, а вызывалась другая процедура, т.е. по форме нашел значение из списка нажал Enter - результат - вызов процедуры.