Check for a specific month in a column

[sofas]

Hi I have several dates in column L
Day/date/month/year

Before executing a specific code, what is required is to verify that there is at least one month that corresponds to cell K2, which contains the name of the month only. If it is not found, the execution of the macro is canceled. I tried this, but it does not respond.

Sub example()
Set ws = ActiveSheet
Dim StartDate As Range
Dim rgFound As Range
  lastrow = ws.Range("L" & Rows.Count).End(xlUp).Row

    Set StartDate = ws.Range("k2")
     Set rgFound = Range("L1:l" & lastrow).Find(What:=Month(StartDate), LookAt:=xlWhole, LookIn:=xlValues)
                                              

        If rgFound Is Nothing Then
            MsgBox "The date Not Found.", vbOKOnly + vbExclamation
            Exit Sub
        End If
'When checking for the same month

      'Run macro
    '  ....................................


End Sub

 

[Jeffrey Mahoney]

Sofas,

What values do you have in the cells you are searching? Do you have dates, or text with the month names? What you are searching for is a number representing the month number. If you have dates or month names like Jan or January, the search will fail.

 

[Jeffrey Mahoney]

This may work if the search range is full of dates

Sub example()

  Dim StartDate As Range
  Dim rgFound As Range
  Dim cel As Range
  Dim Rng As Range
  
  Set ws = ActiveSheet
  
  lastRow = ws.Range("L" & Rows.Count).End(xlUp).Row

  Set StartDate = ws.Range("k2")
  Set Rng = Range("L1:l" & lastRow)
  For Each cel In Rng
    If Month(cel) = Month(StartDate) Then
      Set rgFound = cel
      Exit For
    End If
  Next cel

  If rgFound Is Nothing Then
      MsgBox "The date Not Found.", vbOKOnly + vbExclamation
      Exit Sub
  End If


End Sub

 

[sofas]

Sofas,

What values do you have in the cells you are searching? Do you have dates, or text with the month names? What you are searching for is a number representing the month number. If you have dates or month names like Jan or January, the search will fail.

Yes, I have the dates like this, for example. The value of research is February
Monday February 26, 2024

 

[Jeffrey Mahoney]

Please look at what is in the cell, not displayed. When you edit the cell, what shows? That my just be a number format displaying the date like that.

 

[sofas]

This may work if the search range is full of dates

Sub example()

  Dim StartDate As Range
  Dim rgFound As Range
  Dim cel As Range
  Dim Rng As Range
 
  Set ws = ActiveSheet
 
  lastRow = ws.Range("L" & Rows.Count).End(xlUp).Row

  Set StartDate = ws.Range("k2")
  Set Rng = Range("L1:l" & lastRow)
  For Each cel In Rng
    If Month(cel) = Month(StartDate) Then
      Set rgFound = cel
      Exit For
    End If
  Next cel

  If rgFound Is Nothing Then
      MsgBox "The date Not Found.", vbOKOnly + vbExclamation
      Exit Sub
  End If


End Sub

Thank you, it was successful. When editing the search cell or search column, the date is formatted in this format: 02/26/2024
In general, the code works very well for me

 

[Jeffrey Mahoney]

I'm glad I could help