Страницы: 1
RSS
Как сделать историю значений с помощью макроса?
 
Приветствую Вас уважаемые знатоки экселя и макросов в нем! Есть такая проблема, совсем не умею с макросами работать. Прошу помощи! Есть значение, которое каждый день обновляется по Power Query. Ниже, с помощью макроса нужно создать историю значений максимальной давностью в 35 дней.

Например: 12. февраля, значение в ячейке 1,17. Но 13. февраля это значение обновляется на 1,20, и макрос копирует это новое значение в таблицу ниже под старое значение, 14. февраля значение опять меняется на 1,10 и макрос опять копирует новое значение в таблицу ниже под старое. и так далее.

Прикреплю также файл для примера! Заранее спасибо!
 
А вариант с историей изменения ячейки в примечании не рассматривали которому здесь учили?
https://www.planetaexcel.ru/techniques/5/208/
 
lesha1992, т.к. нет уверенности, что выгрузка через PQ генерит событие на листе, то вариант для кнопки:
Код
Sub iCopy()
Dim aa As Range, a&
With ActiveSheet
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  a = .Cells(.Rows.Count, "B").End(xlUp).Row
  Set aa = .Range("B11:E" & a + 1)
  If aa.Rows.Count > 1 Then
    If aa(aa.Rows.Count - 1, 3).Value <> [B4].Value Then
      If aa.Rows.Count > 35 Then
        Intersect(aa.Rows("2:35"), aa).Copy .[B11]
        aa(35, 1).Value = Date: aa(35, 3).Value = [B4].Value
      Else
        aa(aa.Rows.Count, 1) = Date: aa(aa.Rows.Count, 3) = .[B4].Value
      End If
    End If
  Else: aa(aa.Rows.Count, 1) = Date: aa(aa.Rows.Count, 3) = .[B4].Value
  End If
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End With
End Sub
Изменено: Anchoret - 12.02.2019 22:10:01
 
Anchoret, спасибо Вам! Так как макросы для меня это темный лес, попробую Ваш вариант!

Цитата
GRIM написал: вариант с историей изменения ячейки в примечании...
Спасибо, этот вариант я не видел, почитаю! До написания этого поста я пробовал один другой вариант, скачал файл экселя с макросом с этого сайта, потом пробовал этот макрос перенести уже в свой файл но не получилось!  
Изменено: lesha1992 - 12.02.2019 22:14:25
 
Anchoret, Ваш вариант подходит больше всего! Но как сделать чтобы это происходило не по кнопке а спустя какое-то время после открытия файла? Просто у меня этот запрос на значение происходит сразу после открытия файла и было бы хорошо чтобы после этого спустя минуту сам запускался этот макрос?
 
Цитата
lesha1992 написал:
сразу после открытия файла и было бы хорошо чтобы после этого спустя минуту сам запускался этот макрос?
В модуль книги:
Код
Private Sub Workbook_Open()
Application.OnTime Now + TimeValue("00:01:00"), "iCopy"
End Sub
 
Anchoret, спасибо сработало!

Могу задать еще один вопрос? А допустим если этого 13 февраля значение останется тоже что и 12, макрос не сработает! Что нужно добавить в код чтобы он сработал всеравно? Тоесть чтобы получилось :

12.02.19 - 1,17
13.02.19 - 1,17

Просто вся эта затея моя свяана с ценами на топливо а они могут и оставаться на прежнем уровне
Изменено: lesha1992 - 12.02.2019 22:38:44
 
Цитата
lesha1992 написал: спасибо сработало!
Оно всегда срабатывает )
 
Цитата
lesha1992 написал:
чтобы он сработал всеравно?
Убрать условие, где сравнивается с последним занесенным значением.
 
Anchoret, Тоесть это?
Код
If aa(aa.Rows.Count - 1, 3).Value <> [B4].Value Then
 
lesha1992, да + завершающее End IF
 
Доброе время суток.
Цитата
lesha1992 написал:
Есть значение, которое каждый день обновляется по Power Query
Может стоит тогда и историю делать, используя Power Query и подход self reference table? Причём новое значение писать сразу в эту таблицу?
 
Андрей VG, Спасибо за совет! Но мне легче будет сделать по варианту Anchoret'a! Так как я еще не так хорошо шарю в экселе. Работаю по сути только с формулами.

Anchoret, Спасибо Вам большое! Вы меня очень выручили! Хотел раньше такое сделать, да вот не знал как! Теперь знаю.
 
Anchoret, Последний Вам вопрос! Сейчас у меня макрос работает спустя минуту после открытия файла и бывает, что я несколько раз в день захожу в этот файл и он постоянно срабатывает.

Получается такая картина
12.02.19 - 1,17
12.02.19 - 1,17
12.02.19 - 1,17
12.02.19 - 1,17

Как сделать так чтобы макрос срабатывал если меняется дата, которая указана в ячейке В3? Если же дата в ячейке В3 не меняется то он бы не срабатывал.
 
lesha1992,
Код
Sub iCopy()
Dim aa As Range, a&
With ActiveSheet
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  a = .Cells(.Rows.Count, "B").End(xlUp).Row
  Set aa = .Range("B11:E" & a + 1)
  If aa.Rows.Count > 1 Then
    If aa(aa.Rows.Count - 1, 1).Value <> Date Then
      If aa.Rows.Count > 35 Then
        Intersect(aa.Rows("1:34"), aa).Value = Intersect(aa.Rows("2:35"), aa).Value
        aa(35, 1).Value = Date: aa(35, 3).Value = [B4].Value
      Else
        aa(aa.Rows.Count, 1) = Date: aa(aa.Rows.Count, 3) = .[B4].Value
      End If
    End If
  Else: aa(aa.Rows.Count, 1) = Date: aa(aa.Rows.Count, 3) = .[B4].Value
  End If
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End With
End Sub
Изменено: Anchoret - 13.02.2019 13:06:12
 
Anchoret, Благодарю! Работает! Думаю на этом мои вопросы закончились.  
 
lesha1992, исправил код выше. Иначе при скроллинге данных пропадало слияние ячеек.
 
Anchoret, Благодарю!
Страницы: 1
Наверх