Здравствуйте! Подскажите пожалуйста, такая задача возникла передо мной, не могу сообразить как логику построить, формулой ничего не вышло, пришлось обратиться за помощью в написании макроса. Суть: Имеются даты оплаты на листе "Исходный", и даты смены ключевой ставки. Необходимо вставить даты ключевой ставки между соответствующих периодов в которые они попадают, на втором листе я написал пример, как должно получится, желтыми отметил даты ключевой ставки вставленные, и столбец с расчетом количества дней. Спасибо!
Kentavrik7, здравствуйте! Помогать не с чем, т.к. ваших попыток нет. Алгоритм: 1. забираем 2 диапазона в массивы 2. запоминаем первый и последний элементы первого (левого массива) 3. объединяем массивы в один (1 столбец) 4. сортируем полученный массив 3. собираем новый массив из 3ёх столбцов, выводя только даты не ранее и не позднее запомненных в п.1 с соответствующими вычислениями
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, (рабочий файл отличается расположением значений внизу поправки на диапазоны рабочего файла) В диапазоне AH2:AI29 находятся даты ключевых ставок (AH) и сами ставки (AI) соответственно Дата оплаты расположены в 7 столбце
Код
Sub Пеняй_на_пени()
a = Range("AH2:AI29") 'собираем в массив дату и ключевую ставку
For i = 2 To Cells(Rows.Count, 7).End(xlUp).Row 'перебираем все значения по столбцу "ДАТА оплаты"
For n = 1 To UBound(a) 'перебираем с первого по последний элемент массива
If Cells(i, 7) < a(n, 1) And Cells(i + 1, 7) > a(n, 1) Then 'если дата ключевой ставки больше первой даты оплаты и одновременно меньше второй даты оплаты
'Вставляем строку ниже
Rows(i + 1).Insert
Cells(i + 1, 7) = a(n, 1) 'вносим дату ключевой ставки в ячейку
Cells(i + 1, 1) = "Смена ключевой ставки" 'ставим соответствующую надпись
Cells(i + 1, 8) = "=RC[-1]-R[-1]C[-1]" 'вставляем формулу подсчета дней
Cells(i + 1, 9) = a(n, 2) 'вносим в дополнительный столбец саму ключевую ставку
Else
Cells(i + 1, 8) = "=RC[-1]-R[-1]C[-1]" 'иначе, вставляем формулу подсчета дней
' Необходимо так же подтянуть ключевую ставку ближайшую
End If
Next n
Next i
End Sub
Пока что вот так, но пока не могу понять как подтянуть ключевую ставку ближайшую. То есть когда вставляю новую дату, там понятно ключевую ставку я могу подтянуть сразу с датой. А когда не подтягиваю нужно вставить ближайшую к дате ключевую ставку.
Option Explicit
'===========================================================================================
Sub JoinColumns()
Dim dicR As Object, dicAll As Object
Dim arr, x, arr1x()
Dim r&, iMin&, iMax&, t!
t = Timer
Set dicAll = CreateObject("Scripting.Dictionary")
arr = Range("A3:A18").Value2
iMin = arr(1, 1)
iMax = arr(UBound(arr, 1), 1)
For Each x In arr
x = dicAll(x)
Next x
Set dicR = CreateObject("Scripting.Dictionary")
arr = Range("C3:C30").Value2
For r = 1 To UBound(arr, 1)
x = dicAll(arr(r, 1))
x = dicR(arr(r, 1))
Next r
arr1x = dicAll.keys: Set dicAll = Nothing
Sort_Array1x arr1x, 0, UBound(arr1x)
ReDim arr(1 To UBound(arr1x) + 2, 1 To 3)
arr(1, 1) = "Комментарий"
arr(1, 2) = "Дата оплаты"
arr(1, 3) = "Кол-во дней"
r = 1
For Each x In arr1x
If x > iMax Then Exit For
If x >= iMin Then
r = r + 1: arr(r, 2) = x
If dicR.Exists(x) Then arr(r, 1) = "Смена ставки"
If r > 2 Then arr(r, 3) = arr(r, 2) - arr(r - 1, 2)
End If
Next x
Application.ScreenUpdating = False
ActiveWorkbook.Worksheets.Add
ActiveSheet.Tab.Color = vbRed
Cells.VerticalAlignment = xlCenter
Rows(1).HorizontalAlignment = xlCenter
Rows(1).Font.Bold = True
Columns(2).NumberFormat = "dd/mm/yy;@"
With Cells(1, 1).Resize(r, UBound(arr, 2))
.Value2 = arr
.Borders.LineStyle = True
.WrapText = False
.ShrinkToFit = True
End With
Cells.EntireColumn.AutoFit
Application.ScreenUpdating = True
MsgBox "Выгружено строк (без шапки): «" & r - 1 & "»", vbInformation, "Время работы макроса: " & Format$(1000 * (Timer - t), "0 мс")
End Sub
'===========================================================================================
Private Sub Sort_Array1x(arr1x(), l&, u&)
Dim i&, j&, x, y
i = l: j = u: x = arr1x((l + u) \ 2)
Do
Do While arr1x(i) < x: i = i + 1: Loop
Do While x < arr1x(j): j = j - 1: Loop
If i <= j Then y = arr1x(i): arr1x(i) = arr1x(j): arr1x(j) = y: i = i + 1: j = j - 1
Loop Until i > j
If l < j Then Sort_Array1x arr1x, l, j
If i < u Then Sort_Array1x arr1x, i, u
End Sub
'===========================================================================================
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous,Спасибо, намудрили вы правда, не разберусь теперь, хоть комментарии бы оставили, а то исходный файл другие номера столбцов имеет, теперь не понятно как этот код приделать
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Kentavrik7: во втором случае, по итогу он обработал 326 значений из 586
такое возможно только, если у вас в передаваемых диапазонах содержатся дубликаты (словари их удаляют) В примере всё работает корректно? Если да, то все вопросы к вам
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Jack Famous, Я не хотел вас задеть. Удаление дубликатов к сожалению некорректный подход так как, месяцы там повторяются в каждой счет фактуре, то есть удалять их к сожалению нельзя
Kentavrik7: Удаление дубликатов к сожалению некорректный подход
ни в описании, ни в примере ничего подобного нет. У вас почти 400 сообщений — думаю, что сможете заменить словари на массивы, а то потом ещё что-то всплывёт, а я гадать не люблю (например, если одна и та же дата есть в обоих диапазонах) Удачи!
Во всех делах очень полезно периодически ставить знак вопроса к тому, что вы с давних пор считали не требующим доказательств (Бертран Рассел) ►Благодарности сюда◄
Wiss, по моему самое быстрое решение) А не объясните, почему в шаге с добавлением пользовательского столбца в формуле нужно ссылаться на дату предыдущей таблицы?
Подскажите как решить проблему: определяю вначале последнюю ячейку, но так как в цикле вставляются строки происходит проблема что диапазоны не расширяются, добавил строку в цикл внутри Lastrow = Lastrow + 1, но верхний главный цикл его игнорирует, а берет изначальный диапазон
Код
Sub Пеняй_на_пени()
a = Worksheets("Лист1").Range("A2:B29") 'диапазон ключевой ставки
Lastrow = Cells(Rows.Count, 7).End(xlUp).Row
For i = 2 To Lastrow 'перебираем все строки до последней заполненной
n = 0
'If i = 585 Then Stop
Debug.Print i
'================================================================================================================================================================================================
'Цикл до выполнения условия
Do
n = n + 1
If n > UBound(a) Then 'если номер элемента не больше чем элементов в массиве
n = 1
Exit Do
End If
Loop Until Cells(i, 7) < a(n, 1) And Cells(i + 1, 7) > a(n, 1)
'================================================================================================================================================================================================
'Если дата текущая больше следующей (проверка на новую СФ)
If Cells(i, 7) <= Cells(i + 1, 7) Then
'Повторно проверяем условие
If Cells(i, 7) < a(n, 1) And Cells(i + 1, 7) > a(n, 1) Then
Rows(i + 1).Insert 'создаем строку
Lastrow = Lastrow + 1
Cells(i + 1, 7) = a(n, 1) 'вносим дату ключевой ставки
Cells(i + 1, 1) = "Смена ключевой ставки" ' пишем заметку
Cells(i + 1, = "=RC[-1]-R[-1]C[-1]" 'вставляем формулу подсчета количества дней
Cells(i + 1, 9) = a(n - 1, 2) ' вносим ключевую ставку
Else
'Cells(i, = Application.Index(Worksheets("Лист1").Range("A2:B29"), Application.Match(Cells(i, 7), Worksheets("Лист1").Range("A2:A29"), 1), 1)
' находим ближайшую ключевую ставку и вносим ее
Cells(i + 1, = "=RC[-1]-R[-1]C[-1]" 'вставляем формулу подсчета количества дней
Cells(i + 1, 9) = Application.Index(Worksheets("Лист1").Range("A2:B29"), Application.Match(Cells(i, 7), Worksheets("Лист1").Range("A2:A29"), 1), 2)
End If
Else
End If
Next i
'================================================================================================================================================================================================
End Sub
Что то так он вообще не хочет идти Моя ошибка была. Он та идет, но проблема осталась. Последним он считает начальный диапазон, а то что там строки добавляются ему все равно