CODEWITHSUNDEEP
excelvba
Jaggu Shah
Jaggu Shah

Reputation: 1

How to solve type mismatch (Run Error 13) for Date in VBA?

I am writing a VBA code in my excel. I have employees date of birth on Row F. Now, using VBA, I want phrase called "Happy Birthday" on column G for those employees who have birthdays. My table starts from row 6 to row 50. I wrote the following code but it always gives error in Month(Range("f" & y)). The month function gives me error. If, for example, I only write Range("f" & y), it will work fine. This means the Date of birth on column F isn't being recognized as DATE in my VBA (they are in date format in excel). There is a mismatch for sure. Can someone please help me how to fix this issue (using for next function as shown below)?>

Sheets("Employees").Select
For y = 6 To 50
If Month(VBA.Date) = Month(Range("f" & y)) Then
Range("g" & y).Value = "HBD"
Else
Range("g" & y).Value = "No hbd"
End If
Next y

Note: A). I am looking at the month of date of birth only for wishing happy birthday and I am not looking at day. B). I want message called HBD or NO HBD to be posted on column G for each employee based on their DOB given on Column F .

Upvotes: 0

Views: 549

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19767

With a formula I think you should be able to do it with =IF(MONTH(TODAY())=MONTH(F6),"HBD","No HBD").

For a VBA solution use:

Sub Test()

    Dim y As Long

    With ThisWorkbook.Worksheets("Employees")
        For y = 6 To .Cells(.Rows.Count, 6).End(xlUp).Row
            If Month(Date) = Month(.Cells(y, 6)) Then
                .Cells(y, 7) = "HBD"
            Else
                .Cells(y, 7) = "No HBD"
            End If
        Next y
    End With

End Sub

NB: Cells(Row, Column) is used in place of Range - easier for referencing a single cell using row/column numbers.

Upvotes: 1

Related Questions