VBA Dates

[mmetzinger]

Ok, I am trying to create a custom variable with data pulled from other variables but I can't get excel to accept it. I keep getting a type mismatch but can't figure out how to get past it

Public StartDate As Date
StartDate = "11/1/2010"

Dim Month As String
Month = DatePart("M", StartDate)

Dim Year As String
Year = DatePart("YYYY", StartDate)

Dim MonthYear As Date
MonthYear = Month & "/" & "*" & "/" & Year

Can anyone tell me how to combine the found month and year into the new variable? Is there any easier way to get the date without a value in the day field?

 

[JP2112]

What you've done is create a String that looks like this:

"11/*/2010"

Since this doesn't represent a valid date, you cannot assign this String to a Date-type variable.

 

[mmetzinger]

I figured that much I just need to figure out how to generate a date that has no day value because I need to be able to search a sheet for the last row that has data for that month.

 

[shg]

If you set the date to the last day of the month, then lookup functions would find the largest value <= that date, if the data is sorted ascending by date.

 

[Scott Huish]

Why do you need VBA? This can be done with a formula:

With desired month in H1 and desired year in I1:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1								11	2010
2	11/1/2009	12
3	11/3/2009	15		30
4	11/4/2010	27
5	11/16/2010	30
6	12/1/2010
Sheet1

 

[jasonb75]

using your variables, something like

monthyear = DateSerial(Year(startdate), Month(startdate) + 1, 0)

 

[Scott Huish]

Using those variables, Startdate should be entered as a date literal not a string:

StartDate = #11/1/2010#