ROUNDUP current date before 15th or end of month
I am getting an "expected array" error at Today = Day (Today ()). I don’t know what’s wrong.
Dim Thisday As Integer
Dim Montho As Integer
Dim Yearo As Integer
Dim Lday As Integer
Dim last as Integer
last = Cells(Rows.Count, "A").End(xlUp).Row
Thisday = Day(date)
Montho = Month(date)
Yearo = Year(date)
Lday = Day(Application.WorksheetFunction.EoMonth(Date, -1))
Then column B in excel is populated with the same date, either on the 15th or the last day.
If Thisday <= 15 Then
Range("B2:B" & Last).Value = Montho & "/15/" & Yearo
End If
If Thisday > 15 Then
Range("B2:B" & Last).Value = Montho & "/" & Lday & "/" & Yearo
End If
End Sub
Also, not every month ends on the 30th, so how to get Lday must return as day of the month.
source to share
Use Date
instead Today
.
Don't use the same name as functions, excel messed up.
vba does not EoMonth
, it is part of Application.WorksheetFunction. It also returns a date or twice a non-integer. You will need to get Day
:
Dim Today As Integer
Dim Montho As Integer
Dim Yearo As Integer
Dim Lday As Integer
Dim last As Integer
last = Cells(Rows.Count, "A").End(xlUp).Row
Today = Day(Date)
Montho = Month(Date)
Yearo = Year(Date)
Lday = Day(Application.WorksheetFunction.EoMonth(Date, 0))
If Today <= 15 Then
Range("B2:B" & Last).Value = Montho & "/15/" & Yearo
End If
If Today > 15 Then
Range("B2:B" & Last).Value = Montho & "/" & Lday & "/" & Yearo
End If
The above code returns a string that looks like a date. To return a true date use this:
Dim Today As Integer
Dim Montho As Integer
Dim Yearo As Integer
Dim last As Integer
last = Cells(Rows.Count, "A").End(xlUp).Row
Today = Day(Date)
Montho = Month(Date)
Yearo = Year(Date)
Range("B2:B" & Last).NumberFormat = "mm/dd/yyyy"
If Today <= 15 Then
Range("B2:B" & Last).Value = DateSerial(Yearo, Montho, 15)
End If
If Today > 15 Then
Range("B2:B" & 4).Value = Application.WorksheetFunction.EoMonth(Date, 0)
End If
source to share