Retrieve last day of the month but hitting error
Hi,
I have month and year in the worksheet where I allowed user to select Month and Year and I wanted to retrieve last day of the Month+Year selected.
see below codes
Dim Month As String
Dim Year As String
Dim EndDate As Date
Month = Range("B1").Value 'July
Year = Range("D1").Value '2014
EndDate = DateValue((Month + 1) & "/1/" & Year) - 1
Hitting runtime error '13' : Type mismatch. What am I doing wrong? Please help.
Are the values in B1 and D1 text or numbers?
Thanks,
Solus
Please remember the following:
1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.Highlight the code in your post and press the # button in the toolbar.2. Show appreciation to those who have helped you by clickingbelow their posts.
3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.
"Slow is smooth, smooth is fast."
Hi,
first of all, please use [code] tags around your code.
The problem is that Month is a String and you cannot add 1 to a string, you need to convert it to a number type.
Note that your approach will fail for December, i.e. it's not possible to have date such 13/1/2014.
You should use something like this
if you declare Year and Month as Integers, you can skip type conversion.
WorksheetFunction.EOMONTH(DateSerial(CInt(Year),CInt(Month),1),0)
If you are pleased with a member's answer then use the Star icon to rate it.
If you month "July" in "B1" is a string then ,Perhaps
Regards MickSub MG04Sep49 Dim MyMonth As String Dim Year As String Dim EndDate As Date Dim Mth As Integer MyMonth = Range("B1").Value 'July ' Year = Range("D1").Value '2014 Mth = Month("01-" & MyMonth & "-" & Year) EndDate = DateAdd("d", -1, DateSerial(Year, Mth, "1")) End Sub
If you month "July" in "B1" is a string then ,Perhaps
Regards MickSub MG04Sep49 Dim MyMonth As String Dim Year As String Dim EndDate As Date Dim Mth As Integer MyMonth = Range("B1").Value 'July ' Year = Range("D1").Value '2014 Mth = Month("01-" & MyMonth & "-" & Year) EndDate = DateAdd("d", -1, DateSerial(Year, Mth, "1")) End Sub
Hi all,
Thanks for your reply. Sorry it's my first time posting. Will take note of that.
I'm hitting error type mismatch for both codes below. Month and Year is a String. I'm using Excell 2011 on Mac. Is that the problem?
I tried
and alsoEndDate = WorksheetFunction.EoMonth(DateSerial(CInt(Year), CInt(Month), 1), 0)
Sub MG04Sep49 Dim MyMonth As String Dim Year As String Dim EndDate As Date Dim Mth As Integer MyMonth = Range("B1").Value 'July ' Year = Range("D1").Value '2014 Mth = Month("01-" & MyMonth & "-" & Year) EndDate = DateAdd("d", -1, DateSerial(Year, Mth, "1")) End Sub
I'm not familar with "Mac's " and Excel, although it may be the case.
I can only suggest you open the VB Code Window and step through the code (Clicking "F8") to see where it fails.
If you click help in the Vb Code Window I imagine you can see if those Functions like "Month" and "DateAdd" are supported.
Hi,
it's because you have "July" in B1. My understanding was that you have Month index i.e. 7.
add this to your module
Amend your code as follows:Private Function MonthIndex(strMonth As String) As Variant Select Case LCase(strMonth) Case "january" MonthIndex = 1 Case "february" MonthIndex = 2 Case "march" MonthIndex = 3 Case "april" MonthIndex = 4 Case "may" MonthIndex = 5 Case "june" MonthIndex = 6 Case "july" MonthIndex = 7 Case "august" MonthIndex = 8 Case "september" MonthIndex = 9 Case "october" MonthIndex = 10 Case "november" MonthIndex = 11 Case "december" MonthIndex = 12 Case Else MonthIndex = CVErr(xlErrNA) End Select End Function
Dim Month As Integer Dim Year As Integer Dim EndDate As Date Month = MonthIndex(Range("B1").Value) '7 Year = Range("D1").Value '2014 EndDate = WorksheetFunction.EoMonth(DateSerial(Year, Month, 1), 0)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks