0

Currently, I'm trying to figure a way to look down a single column to find a specific criterion and in this case, I want to find dates that are less than 4/16/20. Once that criteria is met, I want to SUM the cell below for all met criteria in that column. This is an image of my dataset

Dataset.

After doing some googling the closest I found was the code below which leads me to the idea that change information after offset to compute what I want.

Using "If cell contains" in VBA excel

Sub AddDashes()

Dim SrchRng As Range, cel As Range

Set SrchRng = Range("RANGE TO SEARCH")

For Each cel In SrchRng
   If InStr(1, cel.Value, "TOTAL") > 0 Then
      cel.Offset(1, 0).Value = "-"
   End If
Next cel

End Sub

Not sure if I'm on the right track. It would also be nice If I would be able to amend code to check a range vs 1 column, thanks.

SJR
  • 22,986
  • 6
  • 18
  • 26
Freddy Mondragon
  • 3
  • 1

2 Answers2

0
Sub SumByDate()

Dim SrchRng As Range
Dim c As Range
Dim d As Range
Dim lCol As Long
Dim ColLtr As String
Dim SearchDate As Date

lCol = Cells(2, Columns.Count).End(xlToLeft).Column
ColLtr = Split(Cells(1, lCol).Address, "$")(1)
Set SrchRng = Range("B2:" & ColLtr & "2")
SearchDate = DateValue("04/16/2020")


For Each c In SrchRng
Set d = c.Offset(1, 0)
   If c < SearchDate Then
      Sum = Sum + d
   End If
Next c

Range("A4") = Sum

End Sub
Gitty
  • 166
  • 8
  • Thank you, it worked but only for row two (2). I wanted to know how to do what you did for row two (2) but for Col B. Would you be able to add annotations. So I get a better understanding and maybe explain myself better. I like this method too, to calculate individual customers, but I would also like to calculate for all, either in a column or range like B:H, thanks. – Freddy Mondragon May 07 '20 at 22:41
  • @FreddyMondragon you can declare each range separately but that would be time-consuming, especially if your ranges change. Perhaps dialog box to select range is better? I'm posting an idea with explanations as a new answer. – Gitty May 08 '20 at 14:38
0

In this example you need to select the range of dates (1 row at a time only) to run the code. This also allows you to input the date (I'm assuming it changes over time so this should be helpful)

Sub SumByDate()

Dim DateRange As Range
Dim c As Range
Dim d As Range
Dim SearchDateInput As String
Dim SearchDate As Date

Set DateRange = Application.InputBox("Select a range", "Get Range", Type:=8) 'Select Date Range
SearchDateInput = Application.InputBox("Enter A Date") 'enter as mm/dd/yyyy
SearchDate = DateValue(SearchDateInput) 'this converts date entered as string to date format


For Each c In SrchRng 'for each date in list of dates
Set d = c.Offset(1, 0) 'pionts out cells to sum- in this case the cell beneath the date
   If c < SearchDate Then 'if date is less than input date
      Sum = Sum + d 'sum the date if true
   End If
Next c 'goes to next date in row

DateRange.Cells(1, 1).Offset(2, -1) = Sum 'inputs all summed values into cell that is two down, one to the left of first date cell

End Sub
Gitty
  • 166
  • 8