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

11589

[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.