sum of range amounts realated to the beginning of the month and today date

Question

I wrote this code, but it gives an error. Can you please help me to correct it? I have two columns in excel: one shows the dates (each day, one after another), so that the last date in the column is "yesterday", and the second column shows relevant amounts. I need to write a formula by VBA calculating the sum of those amounts from the beginning of the month till the last row (last date, which is "yesterday"). As you may undertand the beginning of the month changes every month and the last date changes every day. - Thank you very much in advance!

Dim x As Long
x = Month(Workbooks("Reports.xlsm").Worksheets("MACRO").Cells(2, 3))
Range("F64").Formula = "=Sum(" & Range(Cells(Worksheets("A").Cells(Rows.Count, 4).End(xlUp).Row, 4), Cells(Worksheets("A").Cells(Rows.Count, 4).End(xlUp).Row - x, 4))

Answer 1

Here's a way to find the 1st day of the month and yesterday's rows.

Sub FindYesterdayAnd1stDayofMonth()

' Turn Off screenupdating and calc to speed up code
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Dim wS As Worksheet
Set wS = ActiveSheet

Dim rFound As Range
Dim rS%, rE%

Dim M As Integer, Y As Integer


' Find 1st day of current month
M = Month(Now - 1)
Y = Year(Now - 1)

' Look in column A. Using a Range object to do some error checking when it doesn't work
Set rFound = wS.Columns(1).Find(What:=DateSerial(Y, M, 1), LookIn:=xlValues)

If rFound Is Nothing Then
    MsgBox "Date '" & DateSerial(Y, M, 1) & "' not found"
Else
    rS = rFound.Row
End If



' Find yesterday
Set rFound = wS.Columns(1).Find(What:=Date - 1, LookIn:=xlValues)

If rFound Is Nothing Then
    MsgBox "Date '" & Date - 1 & "' not found"
Else
    rE = rFound.Row
End If


' Turn updating and calc back on
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

Answer 2

Yesterday is a Day(Now-1). So:

x = Day(Now-1)-1