1st date of (any) month macro

[flamme20]

Can anyone suggest pls how to determine the following rule in macro:

If Range ("A1").Value = first date of any month (ex. 1st of July, 1st of August etc.) Then
Range("B2:C7").Select
Selection.Copy
Range("J2:K7").Select
ActiveSheet.Paste

I'm interested in determining the first date of any month, can I do it this way?

 

[David_cze]

Ehm... And which format is A1 in? If in numeric, I would convert it to DD/MM/YYYY format and then track if first character of value sting is "1"

 

[anandvarma]

Try eomonth function
=eomonth(a1,-1)

 

[David_cze]

Sub GetFirstDay()
    res = Format(Cells(1, 1).Value, "dd/mm/yyyy")
    Cells(2, 1).Value = res
    If ((Left(res, 2) = "01") And ((Mid(res, 2, 1) = "/") Or (Mid(res, 3, 1) = "."))) Then
        Cells(3, 1).Value = 1
    Else
        Cells(3, 1).Value = 0
    End If
    
End Sub

The If clause is bit more variant to fit into multiple locales (for example, in my country, Excel would return 01.05.2011 instead of 01/05/2011).

But anyway, this should work.

Cells:
1,1 - input
2,1 - just check the result of format conversion
3,1 - 1 if first day, 0 if not...

 

[Rick Rothstein]

Can anyone suggest pls how to determine the following rule in macro:

If Range ("A1").Value = first date of any month (ex. 1st of July, 1st of August etc.) Then
.....
.....

I'm interested in determining the first date of any month, can I do it this way?

Assuming A1 contains a real date, use this test...

If Month(Range("A1").Value - 1) <> Month(Range("A1").Value) Then

The above If..Then test will evaluate True only when the date in A1 is the first of the month.

 

[David_cze]

Yea we still kinda need to figure out what is the input of A1, cause if anyone inputs "somethingstupid" instead of "01/01/2011", the Month() will not like it

 

[Rick Rothstein]

Yea we still kinda need to figure out what is the input of A1, cause if anyone inputs "somethingstupid" instead of "01/01/2011", the Month() will not like it

I presume you are responding to my suggestion. If so, then what about handling it this way...

If IsDate(Range("A1").Value) Then
  If Month(Range("A1").Value - 1) <> Month(Range("A1").Value) Then
    '
    '  Your existing code goes here
    '
  End If
Else
  '  I presume you would want to handle the problem differently than this ;-)<G>
  MsgBox "You entered 'somethingstupid'."
End If

 

[flamme20]

I presume you are responding to my suggestion. If so, then what about handling it this way...

If IsDate(Range("A1").Value) Then
  If Month(Range("A1").Value - 1) <> Month(Range("A1").Value) Then
    '
    '  Your existing code goes here
    '
  End If
Else
  '  I presume you would want to handle the problem differently than this ;-)<G>
  MsgBox "You entered 'somethingstupid'."
End If

Thanks, Rick, that goes well and for the fun moment at the end of the working week, I appreciate it!!!

 

[Rick Rothstein]

Thanks, Rick, that goes well and for the fun moment at the end of the working week, I appreciate it!!!

You are welcome, but I have a change for you to consider. For some reason, my mind went for the month difference idea I posted... it is an adaptation for finding out if a date is the last day of the month (check the date's month against the month for the day after the day) which needs the trick I posted because the last day of each month tends to vary; however, you asked about the first day of the month... that is much simpler to check. Instead of this statement...

If Month(Range("A1").Value - 1) <> Month(Range("A1").Value) Then

Use this...

If Day(Range("A1").Value) = 1 Then

I can't believe I went for the more complicated method originally.

 

[flamme20]

You are welcome, but I have a change for you to consider. For some reason, my mind went for the month difference idea I posted... it is an adaptation for finding out if a date is the last day of the month (check the date's month against the month for the day after the day) which needs the trick I posted because the last day of each month tends to vary; however, you asked about the first day of the month... that is much simpler to check. Instead of this statement...

If Month(Range("A1").Value - 1) <> Month(Range("A1").Value) Then

Use this...

If Day(Range("A1").Value) = 1 Then

I can't believe I went for the more complicated method originally.

Searching for complicated ways returns good results.