Настройте существующую формулу на основе последнего добавленного столбца и даты окончания предыдущего месяца с помощью VBA
В настоящее время я создаю макрос для обновления файла инвентаризации, который обновляется еженедельно. После того как все новые данные за неделю добавлены, мне нужно настроить формулу в столбце итогов («MTD Chg»), чтобы она использовала данные за последнюю неделю предыдущего месяца и самый новый добавленный столбец. Мне удалось выбрать правильные ячейки в столбце «Изменение MTD», но теперь я застрял из-за того, что диапазоны столбцов «Изменение MTD» и предыдущих месяцев являются динамическими.
Sub try_to_update_formula()
Sheets("Summary").Select
Cells.Find(What: = "MTD Chg", After:=[A1], LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Dim rwRng As Range, curCol As Long
curCol = ActiveCell.Column
Set rwRng = Union(Cells(51, curCol), Cells(52, curCol), Cells(53, curCol), Cells(54, curCol), Cells(58, curCol), Cells(59, curCol), Cells(60, curCol), Cells(61, curCol), Cells(62, curCol), Cells(63, curCol), Cells(64, curCol), Cells(65, curCol), Cells(68, curCol), Cells(69, curCol))
rwRng.Select
Приведенный выше код ищет столбец с «MTD Chg» и выбирает вызванные ячейки в этом столбце — диапазон столбцов «MTD Chg» будет меняться каждую неделю, когда новый столбец текущей недели вставляется справа. На приложенной фотографии мне нужно обновить формулу, чтобы использовать N51 вместо M51; Новая формула в ячейке O51 будет иметь вид =L51-N51. Еще одна проблема, с которой я столкнусь при смене месяца, — это обновление формулы, основанное на последней неделе предыдущего месяца. Пример: при обновлении файла в первую неделю апреля формула столбца «MTD Chg» должна быть следующей: «Столбец последней недели марта — столбец 1-й недели апреля».
Есть какие-нибудь советы о том, как это сделать? Пожалуйста, дайте мне знать, если вам нужна дополнительная информация.
пример того, как выглядит мой лист
ОБНОВЛЯТЬ:
Мне удалось выяснить, как заставить вторую часть формулы адаптироваться к новому добавленному столбцу!
rwRng.Formula = "=L51-RC[-1]"
Я до сих пор не уверен, как заставить формулу извлечь последнюю неделю столбца предыдущего месяца, когда начинается новый месяц, т. е. если текущий месяц — март, то последняя неделя столбца февраля должна быть ячейкой «L51». , то в апреле в столбце последней недели марта должна быть ячейка «L51».
Мне нужна последняя неделя предыдущего месяца — (curcol-1) в ячейках curcol. Используя пример на фотографии, мне нужно изменить формулу с =L51-M51 на L51-N51, поскольку N51 — это новый добавленный столбец. Другая проблема, с которой я столкнусь, заключается в том, что когда текущий месяц заканчивается и мы добавляем столбец для нового месяца, мне нужна формула «Последняя неделя предыдущего месяца - (curcol-1)». это помогает? Мне нужна формула, которая будет представлять собой последнюю неделю предыдущего месяца минус самый последний столбец в текущем месяце.
Похоже, вам нужны и столбец, и строка «Mtd Chg». Вы хотели бы выполнить обратный цикл и найти первый экземпляр Month(x)-Month(y) = 1 и зафиксировать это значение.
Ответы 1
Две части ответа:
- Формулы R1C1 творят чудеса.
- Вы хотите определить, где находится последний месяц по отношению к Mtd Chg.
Я сгенерировал приведенный ниже код в целях тестирования, хотя для существующего кода я использовал точечную нотацию, чтобы исправить неквалифицированные ссылки .Cells().
With Sheets(1)
Dim MtdChgCell As Range: Set MtdChgCell = .Cells.Find("MTD Chg")
Dim MtdChgCol As Long: MtdChgCol = MtdChgCell.Column
Dim MtdChgRow As Long: MtdChgRow = MtdChgCell.Row
Dim rwRng As Range: Set rwRng = Union(.Cells(51, MtdChgCol), .Cells(52, MtdChgCol), .Cells(53, MtdChgCol), .Cells(54, MtdChgCol), .Cells(58, MtdChgCol), .Cells(59, MtdChgCol), .Cells(60, MtdChgCol), .Cells(61, MtdChgCol), .Cells(62, MtdChgCol), .Cells(63, MtdChgCol), .Cells(64, MtdChgCol), .Cells(65, MtdChgCol), .Cells(68, MtdChgCol), .Cells(69, MtdChgCol))
'Dim rwRng As Range: Set rwRng = .Cells(MtdChgRow + 1, MtdChgCol) 'for testing
Dim deltaRow As Long: For deltaRow = MtdChgCol - 1 To 1 Step -1
If Month(.Cells(MtdChgRow, MtdChgCol - 1).Value) - Month(.Cells(MtdChgRow, deltaRow).Value) = 1 Then Exit For
Next deltaRow
rwRng.Formula = "=RC" & deltaRow & "-RC[-1]"
End With
Обновлено: rwRng.Formula = "=RC[-1]-RC" & deltaRow изменено на rwRng.Formula = "=RC" & deltaRow & "-RC[-1]".
Большое спасибо! Единственная загвоздка в том, что мне нужно перевернуть формулу. Судя по фотографии, которую вы включили, мне нужно, чтобы моя формула читалась = $E3-I3. Я попытался переключить оператор If и rwRng.Formula на = "=RC-RC[-1]", но это выдало ошибку "несоответствие типов".
@healeydm rwRng.Formula = "=RC" & deltaRow & "-RC[-1]" должно быть то, что ты написал, верно?
@healeydm Всегда пожалуйста! Пожалуйста, отметьте ответ как ответ (отметьте галочкой рядом с ответом), чтобы он был помечен соответствующим образом в очереди вопросов.
Итак, вы хотите
curCol - 1иcurCol?