Spyderboyy
04-19-2014, 11:38 AM
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
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