VBA to search a column for the first instance of a date within a designated month

[DJhuffman]

I'm sure that title makes this sound far more complicated than it is (at least, I hope so).

To give some background, I have a spreadsheet that lists data chronologically. Out of that data, I have a formula that utilizes just the data from the current month, for example:

=SUM(G39:H55)-SUM(X39:X55)

covers the month of September. However, when I update the data in this spreadsheet using my current macro, old dates are removed and new dates are added. Is there a way that I can search the date column (D) for the row containing the first time a date in Sept is entered and assign that to a variable that I can then substitute into the formula for the G and X cells? Something along the following lines:

TMONTH = (formula to determine first row in col D containing a date in Sept [not necesarily the 1st])
Range("K60") = "SUM(G" & TMONTH & ":H55)-SUM(X" & TMONTH & ":X55)"

Any help would be appreciated. The people on the forums have been so helpful when I've hit a roadblock in the past!

 

[Joe4]

Are you just trying to get the SUM of a particular month?
If so, instead of doing something like that, why not use a SUMIF, SUMIFS, or SUMPRODUCT formula to sum all the records where your designated criteria (month) is met?
Something like this: https://exceljet.net/formula/sum-by-month

 

[DJhuffman]

Ok. I gave this a try. But unless I'm mistaken, to make this work, the resulting formula would look like:

=SUMIFS(G2:G71,D2:D71, ">=" & "9/1/2019")+SUMIFS(H2:H71,D2:D71, ">=" & "9/1/2019")-SUMIFS(X2:X71,D2:D71, ">=" & "9/1/2019")

In which case, my macro would need to incorporate some code to update the Date each month:

Range("K60") = "=SUMIFS(G2:G71,D2:D71, ">=" & Month(Date) - 1 & "/1/" & Year(Date))+SUMIFS(H2:H71,D2:D71, ">=" & Month(Date) - 1 & "/1/" & Year(Date))-SUMIFS(X2:X71,D2:D71, ">=" & Month(Date) - 1 & "/1/" & Year(Date))"

Though this code generates a type mismatch error. Any thoughts?

 

[My Aswer Is This]

Why did you not answer the question in Post 2 ?

 

[DJhuffman]

My apologies. As to whether I am trying to get the SUM of a particular month, I believe the answer is yes, though that month is always the month prior to the date I run the update (ie. I want the sum of September dates when I run the report in October). The Reports will never contain dates after the date of concern (the report in the earlier example may have dates from April, but not October).

I hope this will help.