По работе подгружаю данные из базы в одну табличку. Иногда подгружать нечего, но это не означает, что данные учитывать не стоит. Например вчера данные были, сегодня нет и можно предположить, что их более не существует, можно не учитывать в расчетах и все такое. Но в реальности этот перерыв в один максимум два пропуска. Как можно формулой или макросом протягивать имеющееся значение на две ячейки ниже (не более двух) при условии, что они пустые? Действие циклично, делается каждый день. Сумбурно получилось, но пример в приложении Заранее спасибо
Добрый день. У меня имеется выгрузка отвратительнейшего формата. Она ужасна и по структуре и по виду и по частоте предоставления. Я не представляю как ее можно сделать вменяемым плоским видом. Задача - сделать плоскую таблицу по дням. Пример в приложении и на пером листе одна строчка желтого цвета желаемого результата. Файл выгружается ежедневно. Столбцы постоянно едут. Количество именно дней что-то около двух недель, но это не точно. Собирать надо данные со всех листов в один единый массив. В данный момент в папке лежит пара сотен файлов с повторяющимися данными. Я смог макросом наковырять данных, но все рушится при проверке дат, не тех столбцов и тд. Не получилось короче. Буду рад любым предложением решения. Если задача слишком сложная, то прошу перенести в платный раздел.
Добрый день, У меня не получается проверить опытным путем мой вопрос, поэтому я задаю его тут. Имеется макрос который делает некоторые действия, не особо важно какие. Исполняется он в другой книге Excel. В другой книге действия происходят над таблицей - результатом запроса PQ, который обычно исполняется около минуты или двух. Если макросу приказать "рефреш ол", начнется обновление данных, в том числе и запросов PQ, то дождется ли макрос завершения обновления или кинет "обновление" и стремительно вытащит не обновлённые данные? Если второе, то как "дождаться обновления" и вытащить обновленные и свежие данные? Заранее спасибо.
После написания продолжил поиски и похоже нашел (за день не нашел) Решение. Если это так, то тему можно удалять.
Добрый день. В силу необходимости пользуюсь функцией "предсказ" для определения наклона кривой значений за три дня. Появилась необходимость делать тоже самое, но в рамках одного дня с учетом времени. Какой функцией лучше это сделать? ""НАКЛОН"? Я предполагаю, что изначально надо собрать массив по уловию, но не понимаю как увеличить количество строк в нем (может я изначально не прав). Затем сортирануть его от меньшего к большему (от прошлого к настоящему)
Код
Function SortDescending(arr As Variant, col_index As Long)
Dim i As Long
Dim j As Long
Dim temp As Variant
For i = LBound(arr, 1) To UBound(arr, 1) - 1
For j = i + 1 To UBound(arr, 1)
If arr(j, col_index) > arr(i, col_index) Then
temp = arr(i, 0)
arr(i, 0) = arr(j, 0)
arr(j, 0) = temp
temp = arr(i, 1)
arr(i, 1) = arr(j, 1)
arr(j, 1) = temp
End If
Next j
Next i
End Function
После чего передать в функцию листа если это возможно WorksheetFunction.Slope. Прошу помощи в формировании макроса для решения этой задачи Приложенный файл показывает желаемый результат с выборкой одного месяца
День добрый, Я не совсем уверен как сформулировать вопрос в данной ситуации, но если коротко, то "в чем ошибка?". При присвоении olBody типа Outlook.HTMLBody я получаю ошибку. Вроде все возможные настройки включены, но уходит в ошибку. Что я делаю не так?
Добрый день. Как можно найти и раскрасить все цифры в ячейке в зависимости от знака? У меня есть код, который просто может красить цифры(использовал чтоб сделать жирным), работает нормально, но теперь надо изменить цвет.
Код
Sub HighlightNumericPartInColumn()
Dim i As Long
Dim lastRow As Long
Dim regex As Object
Dim match As Object
Dim cellValue As String
Set regex = CreateObject("vbscript.regexp")
regex.Pattern = "\d+" ' ищем все последовательности цифр
lastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row ' определение последней заполненной строки в столбце A
For i = 1 To lastRow
cellValue = ActiveSheet.Cells(i, "A").Value ' читаем значение ячейки в строке i
Set match = regex.Execute(cellValue) ' ищем все соответствия регулярному выражению
For Each m In match ' проходимся по всем найденным соответствиям
ActiveSheet.Cells(i, "A").Characters(m.FirstIndex + 1, m.Length).Font.Color = vbRed ' изменяем цвет шрифта только для найденной последовательности цифр
Next m
Next i
End Sub
Можно по идее скопировать код и добавить "-" в регулярку и тоже сработает, но мне кажется есть решение в рамках одного кода, у меня не получается. Прошу помощи в решении. Заранее спасибо
Добрый день, форумчане. У меня есть книжка .xlsm в которой есть связь с другой книгой (~45мб). Как только я добавил эту связь (в ячейке сослался на другую книгу формулой) размер моей рабочей книги увеличился на ~25мб. При разрыве связи вес уменьшается. Я не нашел описания почему так происходит, только подобное хранение для PP и данных другой книги. Можно как-то уменьшить размер книги? Не хранить "лишние данные" или тип того? Почему увеличивается размер? P.S. Не уверен, что книги из описания можно приложить как релевантный пример
Добрый день, У меня есть файл с ценами поставщика. Цены обновляются не регулярно, скорее даже произвольно. Мне необходимо вести базу цен по продуктам на каждый день. Как можно с помощью VBA вытянуть значение последней доступной даты подходящей под условие двух других ячеек чтобы формировать прайс на каждый день(максимальная дата или что-то тип того, не знаю)? P.S. Я буду вытягивать данные из другой книги каждое утро и оба файла достаточно тяжеловесные. Заранее спасибо
РЖД предоставляет тарифы для перевозки грузов. Это документ на 600 страниц в котором хрен разберешься. Многие сайты предоставляют возможность онлайн сделать расчет ЖД тарифа по заданным критериям и получить ответ. Например это можно сделать на сайте СПИМЕКС (https://spimex.com/markets/oil_products/rzd/) который отлично отдает все свою данные и отчеты по удобным ссылкам. Мне Необходимо в эксель это реализовать в удобном виде. Как запрос например с критериями или как скачанную базу с Биржи или как-то еще.
Я открыт к любым вариантам предложений реализации, но я вижу это макрос/функция
Добрый день, У меня есть книга с макросами в которую тянутся данные из других книг и формируются некоторые значения. Открытие книг происходит следующим кодом:
Код
Private Sub Auto_Open()
Dim answer As Integer
Name_CMP = ThisWorkbook.Name
answer = MsgBox("Начинаем формировать новый документ?", vbQuestion + vbYesNo + vbDefaultButton2, "Назови цель!")
If answer = vbYes Then
MsgBox "Погнали"
Application.ScreenUpdating = False 'отключаем обновление экрана
Application.Calculation = xlCalculationManual 'Отключаем автопересчет формул
Application.EnableEvents = False 'Отключаем отслеживание событий
ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False 'Отключаем разбиение на печатные страницы
Call DownloadIndex
Call stoks_transfer_to_model
Call loop_atr
Call Get_Links_Open_files 'этот макрос открываем все книги в связях, а их 8 штук. это необходимо для нормальной работы исходной книги
Workbooks(Name_CMP).Activate
Call add_row
Else
MsgBox "Ок, просто посмотреть, понял"
End If
End Sub
Столкнулся с "ошибкой" отработки макросов. На примере
Код
Workbooks(Name_CMP).Activate
Call add_row
Видно, что для корректной работы макроса надо активировать нужную книгу. Я чет не подумал писать код не только с листами, но и с указанием книги. В связи с этим хочу узнать как необходимо править код для отработки макросов в других книгах? Нужно переписать и использовать нечто на подобии "ThisWorkbooks" или можно добавить какую-то паузу при открытии других книг? Их необходимо обновлять каждое утро, хочу завязать макросы на открытие и формировать документ одной клавишей, а не открывать файлы отдельно и запускать макросы в них. Заранее спасибо
Добрый день, скажите пожалуйста как можно рассчитать внутри макроса функцию "максесли" (столбец N "строка в расчет")? Сейчас я просто ставлю формулу на лист (макрос "empty_cells_formulas" внутри файла) и делаю после значениями, но считается слишком долго. В файле будет +-800к строк. Заранее благодарен.
Добрый день, Подскажите пожалуйста как с помощью VBA можно найти нужную строку по нескольким условиям и вытянуть значение из ее столбика? Файл пример с формулой в приложении, но такая реализация невозможна. В файле 800к строк. Мысли по поводу возможной реализации:
Код
Dim Массив As Variant, i As Long
Массив = Range("A1:A10").Value
For i = 1 To UBound(Массив)
If Массив(i, 1) = КакаяТоИнфа Then
End If
Next i
Думаю есть какой-то более простой способ и быстрый. Файнд какой-нибудь с условиями, не знаю. Заранее спасибо.
Добрый день. Достался в наследство файл на 70мб, 10мб которого это какая-то мутная таблица Power Pivot. Сам работал только с обычными сводными, а не Power. Коллеги говорят, что издревле используется только PQ и формулы. Никто ничего про эту байду не знает. Ни то откуда она, ни задействована она ни как она работает. Есть ли способ узнать можно ли безболезненно удалить эту штуку? Например как с формулами (картинка в приложении). Или может на нее PQ ссылается (в чем я тоже не уверен)? Короче степень ее задействованности. Из соображений безопасности и размера файла пример прикрепить не могу. Заранее спасибо.
Добрый день, Я не могу понять как написать функцию ПРЕДСКАЗ.ETS в экселе с переменными в любом виде. Нашел пример синтаксиса в VBA как формулу листа, но использовать ее не получается. Имеются вводные: A1,A2,A3 - переменные с данными за три дня date1,date2,date3 - три даты по которым найдены значения finalddate - дата на которую идет подсчет формат вставки формулы на лист тоже подойдет, но например:
Даже не вставляется. Подскажите пожалуйста верный синтаксис, наверно, для данной формулы с переменными в VBA. Заранее спасибо
upd Предполагаю, что ответ где-то тут
Код
Option Compare Database
Option Explicit
Public Sub test()
Dim app As Excel.Application
Set app = New Excel.Application
Debug.Print app.Evaluate("FORECAST.ETS(42125,{1,2,3,4},{42005,42036,42064,42095})")
app.Quit
End Sub
Добрый день. Встала задачка рассчитать средневзвешенное значение за три прошлых рабочих дня с условием и понять направления тренда этих значений. Звучит приемлемо, но исполнение формулами похоже без массивов сделать невозможно. Пошел писать макрос, там проще:
Код
For i = 9 To 1000
If Range("A" & i) = data_nachalo Then
a = a + Range("b" & i) * Range("c" & i)
b = b + Range("b" & i)
End If
Next i
c = a / b
Получил средневзвешенное за сутки, вторые, третьи, но столкнулся с невозможностью просто рассчитать отклонение. Формулой
Код
ЛИНЕЙН(B1:B5;A1:A5)
все делается элементарно, но надо выгружать данные на лист. Как можно получить итог функции ЛИНЕЙН в VBA? Можно просто передать три числа в эту функцию (как СУММ например) и получить итог? Заранее спасибо.
Имеется файл с ячейкой в которой написано "Выпуск от 05 Январь 2023 г." и это единственное указание на дату в документе. Сейчас чтобы пользоваться этой информацией я обрабатываю ее следующим макросом:
Но мне кажется, что двойная замена это как-то не элегантно и есть решение проще. Или даже с форматом в одну строчку можно. Подскажите пожалуйста прав ли я и каким кодом лучше пользоваться?
P.S. файл пример не прикладываю ибо там будет только текстовая ячейка с "Выпуск от 05 Январь 2023 г."
Добрый день. Досталась рабочая табличка с данными. Данные все которые вы представить можете. Размер какой-то неправдоподобный. Начал удалять данные, листы, связи, все что можно удалить в поисках лишнего веса удалил и остался пустой файл весом 5 мб. Что не так? Где мусора в нем на 5мб? Заранее спасибо
UPD.Да, и файл не прицепляется, не знаю как пример показать в таком случае UPD2 del файла. Ответ найден
Я понимаю, что задача (вопрос) не тривиальна, но может она уже решалась ранее или у кого-то есть имеется макрос/надстройка которая исполняет этот функционал. А теперь к делу. РЖД предоставляет тарифы для перевозки грузов. Это документ на 600 страниц в котором хрен разберешься. Многие сайты предоставляют возможность онлайн сделать расчет ЖД тарифа по заданным критериям и получить ответ. Например это можно сделать на сайте СПИМЕКС который отлично отдает все свою данные и отчеты по удобным ссылкам. Можно и на других, но ссылки приводить не буду, мало ли. И вот вопрос - а в эксель это можно транспортировать? Как запрос например с критериями или как скачанную базу со Биржи или как-то еще? Курс с ЦБ многие дергают. Может кто-то делал такое или уже есть а я найти не могу? Заранее спасибо. В целом если данный вопрос звучит как "дичь" и никому это ранее было не интересно, то можно перенести в работу, но сначала хотелось бы тут ответы получить.
День добрый Скажите пожалуйста как можно в ячейке сделать из даты (как дата не определяется) формата "13-Jan-2022 00:00" с помощью VBA сделать приемлемый формат для дальнейшей работы (13.01.2022 стандартный например)? Спасибо
Добрый день Задача: выгрузить всякую хрень с определенного листа. Листы добавляются раз в пару месяцев. Код выгрузки элементарный, но приходится каждый раз лезть и обновлять на нужный лист значение:
Код
Set ws = Лист20 'Имя листа с которого берутся данные. Изменить при изменении
Это порядком надоело и было принято решение дать возможно выбрать лист при выполнении кода. Для этого очевидно надо собрать все интересующие листы:
Код
Sub chi()
Dim ArraySheets() As String
Dim x As Variant
For Each curSheet In ActiveWorkbook.Worksheets
If curSheet.Name Like "*С месяца *" Then
ReDim Preserve ArraySheets(x)
ArraySheets(x) = curSheet.Name
x = x + 1
End If
Next curSheet
End Sub
Но я не нашел как можно передать найденные значения в "выборную штуку" и можно ли вообще это сделать. Сейчас выбор необходимого объема информации осуществляется через выбор:
Код
NumberOfDays = InputBox("Сколько дней заполнить?")
Но я так понял, что какого-то простого дропдаун списка в инпутбокс впихнуть нельзя. Как можно дать юзеру возможность присвоить значение переменной из выпадающего списка в процессе выполнения кода? Лист не обязательно последний или самый свежий или еще какой-то особенный, нужен именно выбор. Спасибо
Добрый день, Я не совсем знаю как сформулировать данный вопрос, но попробую описать процесс и итог. Мне необходимо скинуть фильтр на листе, после чего выполнить макрос. Если его скидвать кодом:
Код
Лист1.ShowAllData
То при отсутствии фильтрации код уйдет в ошибку. Это можно решить добавив код следующие значение:
Код
On Error Resume Next
Лист1.ShowAllData
И все работает отлично. В связи с чем возникает два вопроса: 1. Есть ли более оптимальные метод сброса фильтра без ошибки? 2. Будет ли On Error Resume Next пропускать все возникающие ошибки дальше в код? Когда стейтмент перестает работать? Мне не надо пропускать все ошибки после, только с фильтрацией. Спасибо
Добрый день, Имеется файл где в одном столбце есть куча произвольно объеденных ячеек (2,5,10,20 построчно) а во втором для каждый ячейки есть некое значение. Как можно пройтись с помощью VBA по списки и в каждый объеденный диапазон приравнять значение? Иногда удаляется первая строка из диапазона и все рушится.
Скажите пожалуйста как можно вытащить минимальное/максимальное значение по имеющемуся ключу с помощью VBA? Ключ повторяется неопределенное количество раз, значения скачут от отрицательных, до н/д (это нормально), необходимо вытащить только данные из видимых ячеек.
Добрый день, форумчане А есть ли для VBA какая нибудь удобная шпаргалка на подобии этой шпаргалки по обычным шорткатам экселя? Я все время забываю основы в виде поиска строки и точек при использовании With. Было бы полезно. Можно на русском или английском, но видеть правильный синтаксис Cells и Range крайне по помешало бы. Не очень долгий поиск дал только ссылки на что-то подобное в формате "отдай мне почту, я тебе что-то дам, но что не скажу". Хочу распечатать и на стенку повесить. Заранее спасибо
День добрый. В определенных ячейках на листе нужно писать при определенных условиях формулу. Формула простая "=b1" и так далее в зависимости от строки. Я хочу определить столбик B так как он может ездить. Я делаю это поиском для поиска номера столбца:
Но я не понимаю как могу использовать эти буквы в формуле на листе:
Код
For i = 1 To 5
On Error Resume Next
If Cells(i, previousPrice).Value = "new" Then 'в каком столбике ищем обозначение новой строки
Cells(i, previousCost).Formula = "=bf" & i 'вот как тут использовать найденные буквы?
End If
Next i
Я хочу сделать словарь, добавить в него ключ и значение из одного файла и при совпадении выгрузить значения в другой. Как добавить к одному ключу несколько значений для последующей выгрузки при совпадении? Данный код нормально работает на одном ключе и одном значении. Решение в виде 10 словарей думаю в корне неверное.
Код
With Workbooks(WBName).Worksheets(WBList)
vVALs = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp)).Value2
For v = LBound(vVALs, 1) To UBound(vVALs, 1)
dict.Item((vVALs(v, 1))) = vVALs(v, 1)
Next v
End With