Сопоставьте год и квартал с даты и вставьте значения

Я пытался найти способ, который бы выполнял следующее сопоставление и вычисления, но не мог найти что-то похожее.

Я новичок в VBA, все, что я хочу, это это.

Worksheet("Sheet1").Range("B4") - это дата, которая равна (31.02.2020), а Sheet2 имеют некоторые данные, как показано на прилагаемом изображении ниже.

Теперь я хочу, чтобы этот код соответствовал ColYear и ColQuarter путем сопоставления даты, поэтому Sheet1 date означает, что год - 2020, а квартал - 1, поэтому код скопирует значения из Col"E" и Col"G", которые опережают тот же год и квартал на дату.

и вставьте эти значения в Worksheet("Sheet3").Range("G10:H10")

Мы будем благодарны за вашу помощь.


excel copy match vba
person Strenuous    schedule 20.01.2021    source источник
comment
Пожалуйста, лучше укажите, что вы хотите добавить. Дата из Sheet1? Активное значение ячейки? Если дата должна быть 31-2-2020, чего никогда не будет (в феврале), должен ли код извлекать год и квартал и использовать их как ссылку для поиска на втором листе?   -  person FaneDuru    schedule 20.01.2021
comment
FaneDuru, да ты прав я поменял ссылку на дату на 31.02.2020 да! code извлечет год и квартал и будет использовать его как ссылку для поиска на втором листе.   -  person Strenuous    schedule 20.01.2021
comment
Затем попробуйте опубликованный мной код ...   -  person FaneDuru    schedule 20.01.2021

Ответы (2)


arrow_upward
1
arrow_downward

Пожалуйста, попробуйте следующий код. Он начинается с активной ячейки Sheet1, где должна быть найдена дата:

Sub testFindDateQAndCopy()
  Dim sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet, lastR2 As Long
  Dim procDate As Date, Qdate As Long, yDate As Long, arr2, i As Long
  
  Set sh1 = Worksheets("Sheet1")
  Set sh2 = Worksheets("Sheet2")
  Set sh3 = Worksheets("Sheet3")
  lastR2 = sh2.Range("B" & rows.count).End(xlUp).row

  If Not IsDate(sh1.Range("B4").Value) Then _
        MsgBox "In cell """ & sh1.Range("B4").Value & """ it is not a date...": Exit Sub
  procDate = sh1.Range("B4").Value 
  Qdate = Int((Month(procDate) - 1) / 3 + 1)
  yDate = Year(procDate)

  arr2 = sh2.Range("B2:G" & lastR2).Value
  For i = 1 To UBound(arr2)
    If arr2(i, 1) & arr2(i, 2) = yDate & Qdate Then
        sh3.Range("G10:H10").Value = Array(arr2(i, 4), arr2(i, 6))
        Exit For
    End If
  Next
End Sub
person FaneDuru    schedule 20.01.2021
comment
Спасибо, что он работает нормально @FaneDuru, но я хочу, чтобы эта штука была динамической, чтобы независимо от даты в коде диапазона Sheet1 она скрывалась в Year и Quarter, чтобы соответствовать ей в Sheet2, потому что текущий код просто работает на 02-29-2020 февраля - person Strenuous; 20.01.2021
comment
@Learning: Не совсем ... Достаточно иметь (любую) дату в активной ячейке! - person FaneDuru; 20.01.2021
comment
Не могли бы вы добавить диапазон (B4) вместо активной ячейки, пожалуйста. - person Strenuous; 20.01.2021
comment
@ Обучение: Конечно, но для такой маленькой модификации было бы хорошо попробовать самому ... Только попробовав, вы можете научиться! Теперь я его адаптирую ... Сделал это, и я прокомментировал строку, проверяющую, является ли активный лист Seet1. Не обязательно, больше ... - person FaneDuru; 20.01.2021
comment
Эта ошибка возникает, даже если я нахожусь в активной ячейке. ibb.co/vqJrMJk - person Strenuous; 20.01.2021
comment
@Learning: это должно означать только то, что значение активной ячейки не является датой. Вы можете указать здесь значение ячейки? Это строка? - person FaneDuru; 20.01.2021
comment
Да, я пытался изменить его с помощью procDate = sh1.Range (B4), но все еще имел ошибку. - person Strenuous; 20.01.2021
comment
@ Обучение: вы ранее просили B7 ... Я адаптировал код с B4. Но это работает, только если это дата в этой ячейке. Если это строка, которую можно преобразовать, опубликуйте ее здесь. - person FaneDuru; 20.01.2021
comment
Правильно, большое спасибо, я понимаю вашу точку зрения. - person Strenuous; 20.01.2021
comment
@ Обучение: Итак, он работает или все еще тестирует? - person FaneDuru; 20.01.2021
comment
@ Обучение: я отредактировал код и адаптировал его, чтобы предупреждать, если в этой конкретной ячейке нет даты ... - person FaneDuru; 20.01.2021
comment
Огромное спасибо за постоянную помощь. Код работает отлично - person Strenuous; 20.01.2021
comment
@ Обучение: Рад, что смог помочь! Дело закрыто... - person FaneDuru; 20.01.2021

arrow_upward
1
arrow_downward

Создайте вспомогательный столбец, который объединяет Year и Quarter, например 2020Q1, например, используя формулу (поскольку совпадение может соответствовать только одному значению, но не 2). Затем используйте метод WorksheetFunction.Match, чтобы сопоставьте этот вспомогательный столбец с тем, что вы получили от даты =YEAR(A1) & "Q" & ROUNDUP(MONTH(A1)/3,0), где A1 - это ваша дата 31-2-2020.

Другое решение может заключаться в том, что если возвращаемые значения содержат только числовые числа и не содержат текста, вы можете использовать функцию SUMIFS:

=SUMIFS(E:E,B:B,YEAR(A1),C:C,ROUNDUP(MONTH(A1)/3,0))

для возврата значения из столбца E. Здесь вспомогательный столбец не требуется, поскольку SUMIFS поддерживает несколько критериев.

person Pᴇʜ    schedule 20.01.2021
comment
Спасибо за помощь, которую вы предоставили @ Pᴇʜ, но я ищу решение на VBA, потому что то, что я опубликовал, составляет 5% моей работы, которая будет прикреплена к исходному коду. Так что мне нужна эта штука в коде. - person Strenuous; 20.01.2021