Рассчитать значения до даты окончания
Я новичок в VBA, и я пытаюсь вычислить значения до/равные дате окончания, запрошенной пользователю, но не могу понять это.
| 03.14.2022 (1 кв.) | 21.03.2022 (R1) |
|---|---|
| 08 (2 кв.) | 10 (R2) |
| 40 (3 кв.) | 20 (Р3) |
моя цель - обработать каждую существующую строку и вернуть сумму для этой конкретной строки в столбце N:N.
поэтому, если пользователь говорит, что дата окончания 21/03, общее значение для 2-й строки должно быть 18, а 3-й строки 60.
Код должен суммировать значения, соответствующие каждой строке существующих ячеек столбца A:A. Я имею в виду, что для третьей строки данные будут суммироваться из третьей строки, расположенной ниже той, в которой хранятся контрольные даты.
Вот мой код:
Sub test()
Dim WS As Worksheet
Set WS = Worksheets("Sheet1")
Const EARLIEST = #1/1/2000#
Dim till As Date
'On Error GoTo Errorhandler
till = CDate(Application.InputBox(Prompt: = "Please enter the date to split the hrs (dd/mm/yyyy):", Type:=2))
If till = 0 Then Exit Sub
If till < EARLIEST Then
MsgBox "You need to insert a valid date dd/mm/yyyy"
Exit Sub
End If
'need to do more spotchecks as check if date exists in the file
ActiveSheet.Range("N1").Select
ActiveCell.FormulaR1C1 = "Total until " & till
Dim lr, lc, lc1 As Long
Dim sum As Long
lr = Range("Q" & Rows.Count).End(xlUp).Row
lc = Cells(2, Columns.Count).End(xlToLeft).Column
lc1 = Cells(1, Columns.Count).End(xlToLeft).Column
Set dates = WS.Range("Q1" & lc1)
For i = 2 To lr
If dates.Value <= till Then
sum = sum + Range("Q2" & lc)
Range("N" & i).Value = sum
End If
Next i
Columns("N:N").Select
Selection.Style = "Comma"
Selection.NumberFormat = _
"_ * #,##0.0_)_ ;_ * (#,##0.0)_ ;_ * ""-""??_)_ ;_ @_ "
Selection.NumberFormat = "_ * #,##0_)_ ;_ * (#,##0)_ ;_ * ""-""??_)_ ;_ @_ "
Columns("N:N").EntireColumn.autofit
'Errorhandler:
'MsgBox "You need to insert a valid date dd/mm/yyyy"
'Resume
End Sub
Range("N" & i).Value = sum Вероятно, это можно упростить до формулы Excel, например =Q2+Q3, и перетащить ее вниз по столбцу. Здесь не нужен VBA.
Selection.NumberFormat повторяется дважды в одном и том же столбце.
спасибо за вашу помощь, очень признателен. Мне нужно знать дату окончания, до тех пор, пока пользователь не захочет суммировать значения. Даты часто меняются, поэтому это не всегда будут одни и те же ячейки. Вот почему я пытаюсь использовать VBA. Я не могу понять, как рассчитать только ячейки до даты окончания, введенной пользователем....
Основываясь на какой логике вы говорите поэтому, если пользователь говорит, что дата окончания 21/03, общее значение для 2-й строки должно быть 18?
Вы понимаете, что делает следующий фрагмент кода: Set dates = WS.Range("Q1" & lc1)? Если вам нужен диапазон столбца «Q: Q», вы должны использовать Set dates = WS.Range("Q1:Q" & lc1)
Set dates = WS.Range("Q1" & lc1) скорее всего должно быть Set dates = WS.Range("Q" & i)@FaneDuru - если пользователь добавит 21/03 в качестве конечной даты, я хочу суммировать значения недели 14/03 (8) и 21/03 (10), так что общее количество будет 18 - я уверен, что код не правильно, но это то, что я стремлюсь сделать. Я новичок в VBA, поэтому я все еще делаю много ошибок:/
Мне все еще не ясно... Что такое 14/03? Разве это не четырнадцатое марта? Если да, то какое участие в неделю? Есть ли числа в столбце «N:N», и вы пытаетесь их пронумеровать, пока не будет найден последний день в «Q:Q»? Если это так, вы должны объяснить, что в словах. Если нет, то я все еще в замешательстве, извините...
Почему бы просто не использовать SUMIF на листе?
Ответы 1
Пожалуйста, попробуйте следующую версию:
Sub testProcessDate_All()
Dim WS As Worksheet: Set WS = Worksheets("Sheet1")
Const EARLIEST = #1/1/2000#
Dim till As Date
till = CDate(Application.InputBox(Prompt: = "Please enter the date to split the hrs (dd/mm/yyyy)" & vbCrLf & _
"or select a cell containing a date in the first row:", Type:=2))
If till = 0 Then Exit Sub
If till < EARLIEST Then
MsgBox "You need to insert a valid date dd/mm/yyyy"
Exit Sub
End If
Dim lc1 As Long: lc1 = WS.Cells(1, WS.Columns.Count).End(xlToLeft).Column
Dim LatestDate As Date
LatestDate = WS.Cells(1, lc1).Value
If till > LatestDate Then MsgBox "The chosen date is larger than last available date...": Exit Sub
'ActiveSheet.Range("N:N").ClearContents 'to be cleared for the next time
WS.Range("N1").Value = "Total until " & till
Dim lr As Long, sum As Long, i As Long, j As Long
If WS.Range("A2").Value = "" Then MsgBox "No values in A:A to run the code...": Exit Sub
lr = WS.Range("A1").End(xlDown).Row 'the last row in A:A (for a table...)
For j = 2 To lr
For i = WS.Range("Q1").Column To lc1
If CDate(WS.Cells(1, i).Value) <= till Then
sum = sum + WS.Cells(j, i).Value
Else
Exit For
End If
Next i
WS.Range("N" & j).Value = sum: sum = 0
Next j
End Sub
большое спасибо за вашу драгоценную помощь. Это то, что я ищу, но числа и даты указаны в столбцах, а не в строках. Таким образом, даты от Q1 до последнего столбца и числа от Q2: последний столбец с данными. Что нужно изменить, чтобы добиться этого?
@V Хорошо. Ваш вопрос не очень ясен, с этой точки зрения и код не помогает его понять... Если теперь итерация должна быть понятна, то как насчет строки кода Range("N" & i).Value = sum? Вы собираетесь поместить промежуточную сумму в столбец N:N? Если да, то с какой строки начать его заполнение? Из первой существующей пустой строки? Со второго ряда?
@V F Адаптировал приведенный выше код для перебора строк и возврата каждой частичной суммы в столбец N:N, начиная со второго ряда. Пожалуйста, протестируйте его и отправьте отзыв.
еще раз спасибо за вашу помощь, я пытался, но это ничего не делает, только после запуска выбирается N: N. Я добавил таблицу в свой вопрос, чтобы было понятнее.
@V F Ваши данные выглядят так, как показано на рисунке? Я имею в виду, существуют ли «(Q1)», «(R1)» такими, какими мы их видим? Если да, то как отформатированы обсуждаемые ячейки? Если вы активируете одну из этих ячеек, содержащих дату, и попробуете Debug.Print Day(activeCell.value), что она вернет в Immediate Window? Если нет ничего конфиденциального, можете ли вы поделиться книгой, которую вы пытаетесь обработать? По крайней мере, обсуждаемый лист и код, который вы используете...
Да, они существуют. Они отформатированы как дата. Я пробовал для Q1 - 14/03/2022, и он вернул 14. К сожалению, я не могу, это файл с работы, и система даже не позволяет мне добавить экран печати....
@V F В таких обстоятельствах я не могу понять, что происходит, и не могу помочь. Я бы только предложил попробовать запустить код построчно, нажимая F8, навести курсор на все переменные, значения ячеек и т. д. и посмотреть, что происходит...
Я скопировал свои даты (из B1) и числа (из B2) в новый файл, и мне удалось заставить его работать для первой строки (A2) - не могу заставить его работать до последней строки, хотя Dim lr, lc1 As Long Dim sum As Long lr = Range("B" & Rows.Count).End(xlUp).Row lc1 = Cells(1, Columns.Count).End(xlToLeft).Даты набора столбцов = WS.Range(" B1", WS.Cells(1, lc1)) For i = WS.Range("B1").Column To lc1 Если date.Cells(1, i).Value <= to Then sum = sum + date.Cells( 2, i). Диапазон значений ("A2"). Значение = сумма Else Exit For End If
Я пробовал то же самое в своем файле, но он ничего не делает. Не могу понять. У меня есть данные в таблице - не уверен, что проблемы связаны с тем, что даты являются заголовками.
@V F Я не уверен, что смогу вас понять ... Дата, которая вам нужна, находится в диапазоне Q1 -> последний столбец в первой строке и отформатирована как Date? Если это так, я не могу помочь, не видя вашей рабочей тетради. Это должно работать, даже если обсуждаемые данные являются частью таблицы. Его также можно передать по-другому, но приведенный выше код также должен работать.
Я послал Вам письмо. еще раз спасибо
ХОРОШО. посмотрю через пару минут...
@V F Пожалуйста, проверьте обновленный ответ и отправьте отзыв. Если это отвечает на ваш вопрос, мы здесь голосуем за код (если больше известности 15) и, что более важно, мы отмечаем флажок слева от кода, чтобы сделать его принятый ответ. Таким образом, кто-то другой, ищущий похожую проблему, будет знать, что код работает.
большое спасибо @faneduru. Он работает отлично! Я принял ответ :)
Range("Q2" & lc)мне кажется не подходит. Например, если ваш последний столбец равен 10, это будет означатьRange("Q210"), что не имеет никакого отношения к тому, что пытается сделать остальная часть кода. Я думаю, что ошибка именно в этом.