Working with dates, ranges, and arrays

[Spyderboyy]

Hello, I am making something of a minor database in Excel 2004 (mac) to help me with my day job. I am making a table that will update my daily sales numbers and data mine certain stuff back to me.

I have a large "YTD" worksheet with each date of 2014 laid out in rows and columns (see screenshot) that will hold all my raw data.

What I am needing to do is update a calendar on another worksheet with totals and a "month to date" total field.

My problem is in trying to .find the date only in the left column by month, and therefore total only the data in the columns labeled with the 30 days of that month. Here is an abridged chunk of my code that I cant seem to solve:

I've added and subtracted lines and have done much trial and error here, so I wouldn't be surprised if there are several missing links by now. I know how to do it without using the array (by searching the whole "A" column), but I need to search only in each month at a time because I want to get to where I can sum the numbers at and before a given day (for an accurate "month-to-date" total, instead of the full monthly total).

The array itself doesn't seem to be the issue (I have a similar array-as-range that is working fine), but the various errors I seem to get are around the mymonth and usedate variables and when I try to run the .find.

Any help would be the bomb.

Dim monthrange(1 To 12) As Variant

Set monthrange(1) = Sheets("YTD").Range("a4:a34")
Set monthrange(2) = Sheets("YTD").Range("a37:a65")
Set monthrange(3) = Sheets("YTD").Range("a68:a98")
Set monthrange(4) = Sheets("YTD").Range("a101:a130")
Set monthrange(5) = Sheets("YTD").Range("a133:a163")
Set monthrange(6) = Sheets("YTD").Range("a166:a195")
Set monthrange(7) = Sheets("YTD").Range("a198:a228")
Set monthrange(8) = Sheets("YTD").Range("a231:a261")
Set monthrange(9) = Sheets("YTD").Range("a264:a293")
Set monthrange(10) = Sheets("YTD").Range("a296:a326")
Set monthrange(11) = Sheets("YTD").Range("a329:a358")
Set monthrange(12) = Sheets("YTD").Range("a361:a391")

d = Sheets("Input").Cells(4, 2).Value   '(Let's say d is today in our case)

    Dim usedate As Date
    Dim Rng As Range
    Dim osht As Sheets
    Dim orng As Range
    Dim mymonth As Range
    usedate = DateValue(d)
    Set osht = Sheets("YTD")
    Set mymonth = Month(usedate)
    MsgBox mymonth
    Set monthrng = monthrange(mymonth)
    Set orng = osht.monthrange
    For Each mycell In orng
    FindString = usedate
            Set Rng = orng.Find(What:=FindString, _
                            LookIn:=xlFormulas, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
            x = Rng.Row
            y = Rng.Column
            Else
                MsgBox "Nothing found"
            End If
        Next

Screenshot 2014-04-19 13.29.30.jpg

[Spyderboyy]

...If it is something that can't be done, can someone help me understand the better approach to what I am attempting?

[SamT]

Please use CamelCase in your code.

osht is declared as a collection of Sheets
Try using LookIn:=xlValues



Your code will have to be updated every leap year and the next.



Check out these UDF's. They all assume that "D1" and Column "A" are formatted as Date

Function BOM(dDate As Date) As Date
'Beginning Of Month
  Dim iDay As String 'For international use
  Dim iMonth As Integer
  Dim iYear As Integer
  Dim sSeparator As String
  sSeparator = "/"
  
  iDay = "1"
  iMonth = CStr(Month(dDate))
  iYear = CStr(Year(dDate))
  
  BOM = CDate(iMonth & sSeparator & iDay & sSeparator & iYear)
End Function

Function BOY(dDate As Date) As Date
'Beginning Of Year
  Dim sYear As String
  
  sYear = CStr(Year(dDate))
  BOY = CDate("1/1/" & sYear) 'Already international
End Function

Function Last30Days(dDate As String) As Date
'The Date 30 days ago. March 1st = Jan 29; Jan 31 = Jan 1st
  Last30Days = dDate - 30
End Function

Function PreviousMonth(dDate As Date) As Date
'Returns proper date of previous month. Mar 1st = Feb 1st; Mar30 = Feb 28 (29 on leap years)
  PreviousMonth = DateAdd(m, -1, dDate)
End Function

Since you are wanting to SUM the values in various Ranges, and since I have no idea what your columns are, I will use XXX for any and all column references.

To Sum only this Month To Date use the above BOM function

Sub Test()
Dim TotalThisMonthToDate
Dim StartRow As Long
Dim EndRow As Long
Const XXX As Long = 4 'Random column number

With Sheets("YTD")
  StartRow = .Range("A:A").Find(BOM(.Range("D1")), LookIn:=xlValues).Row
  EndRow = .Range("A:A").Find(.Range("D1").Value, LookIn:=xlValues).Row
  TotalThisMonthToDate = WorksheetFunction.Sum(Range(Cells(StartRow, XXX), Cells(EndRow, XXX)))
End With
End Sub

For totals spanning months (and their subtotals,) (Function PreviousMonth or Function Last30Days,) (assuming you did not use the Subtotals function to create those subtotals,) you will need to use a Loop and use.

For Each Cel in Range(Cells(StartRow, XXX), Cells(EndRow, XXX))
If TypeName(Cells(Cel.Row, "A")) = "Date" Then Total = Total + Cel.Value

If you used the Subtotal function to get the monthly totals in the Database, you can use Subtotal the same way Sum is used in Sub Test above.

[Aflatoon]

Why use CStr when passing a number value to an Integer variable?

[Bob Phillips]

Function BOM(dDate As Date) As Date
    BOM = dDate - Day(dDate) + 1
End Function

[SamT]

@Aflatoon: Thanks. I went from strings to integers and back and forth trying to write them in the most comprehensible style. I think I would recommend xld,s BOM function anyway.