CODEWITHSUNDEEP
vba
Apurv Pawar
Apurv Pawar

Reputation: 424

date format error-type mismatch

below is the code in which I am getting error at If DOJ = " " Then. The error is "error 13, type mismatch"

I want to check if the cell is empty or not, if empty then change inner color to blue, but it is giving error as type mismatch. Kindly help to resolve.

Sub BGVslaCheck()
fpath = Worksheets("Refrence").Range("C9").Value
fname = Worksheets("Refrence").Range("b9").Value & ".xlsx"
Dim Emp1 As Date
Dim DOJ As Date
Dim Cri As Date
Dim Edu As Date

'On Error Resume Next

Workbooks.Open (fpath & fname)
Worksheets("BGV").Select
Set wf = Application.WorksheetFunction
i = Range("a65536").End(xlUp).Row

For J = 11 To i

        DOJ = Worksheets("BGV").Range("c" & J).Value
        Cri = Worksheets("BGV").Range("g" & J).Value
        Edu = Worksheets("BGV").Range("i" & J).Value
        Emp1 = Worksheets("BGV").Range("K" & J).Value



        a = wf.NetworkDays(DOJ, Cri)
        b = wf.NetworkDays(DOJ, Edu)
        c = wf.NetworkDays(DOJ, Emp)
            Worksheets("BGV").Range("N" & J).Select
                If a > 14 Or b > 14 Or c > 14 Then
                    With Selection.Interior
                    .Color = VBA.RGB(250, 0, 0)
                    End With
                Else
                    With Selection.Interior
                    .Color = VBA.RGB(0, 250, 0)
                    End With
                End If

                'If DOJ = " " Or Cri = " " Or Edu = " " Then
                If DOJ = " " Then
                    With Selection.Interior
                    .Color = VBA.RGB(0, 0, 250)
                    End With
                End If
Next

Workbooks(fname).Close

Upvotes: 0

Views: 162

Answers (1)

Lowpar
Lowpar

Reputation: 907

DOJ is dimmed as a date, while your check is for a string. The below should work alternatively, you could keep it as a date and do if isempty(DOJ) then. 

Sub BGVslaCheck()
fpath = Worksheets("Refrence").Range("C9").Value
fname = Worksheets("Refrence").Range("b9").Value & ".xlsx"
Dim Emp1 As Date
Dim DOJ
Dim Cri As Date
Dim Edu As Date

'On Error Resume Next

Workbooks.Open (fpath & fname)
Worksheets("BGV").Select
Set wf = Application.WorksheetFunction
i = Range("a65536").End(xlUp).Row

For J = 11 To i

        DOJ = Worksheets("BGV").Range("c" & J).Value
        Cri = Worksheets("BGV").Range("g" & J).Value
        Edu = Worksheets("BGV").Range("i" & J).Value
        Emp1 = Worksheets("BGV").Range("K" & J).Value



        a = wf.NetworkDays(DOJ, Cri)
        b = wf.NetworkDays(DOJ, Edu)
        c = wf.NetworkDays(DOJ, Emp)
            Worksheets("BGV").Range("N" & J).Select
                If a > 14 Or b > 14 Or c > 14 Then
                    With Selection.Interior
                    .Color = VBA.RGB(250, 0, 0)
                    End With
                Else
                    With Selection.Interior
                    .Color = VBA.RGB(0, 250, 0)
                    End With
                End If
  'you could try: if isempty(DOJ) then 
                'If DOJ = " " Or Cri = " " Or Edu = " " Then
                If DOJ = " " Then
                    With Selection.Interior
                    .Color = VBA.RGB(0, 0, 250)
                    End With
                End If
Next

Workbooks(fname).Close

Upvotes: 1

Related Questions