vba subsequent IF/AND statments return type mismatch

[Nadine67]

Hello and thank you for any attention my post may receive.

I have the following code which matches the criteria and copies the matches to another sheet. My IF/AND statement executes as it is supposed to for the first instance in the code however the second IF/AND returns a 'type mismatch' error even. I am unsure why this is so and would be so grateful for any help or guidance with this.

Thank you and have a great day!

Sub NewExport()
 
Set session = CreateObject("Notes.NotesSession")
Set db = session.getdatabase("AUMOBNS01/SERVER/IPL", "Business\IPL\IPL00089.nsf")
Set view = db.GetView(("08. Export"))
Set ve = view.AllEntries
Set entry = ve.GetFirstEntry
 
Dim row As Integer
row = 1
 
Dim time1 As Date
time1 = Now()
Application.Calculation = xlCalculateManual
 
While Not (entry Is Nothing)
    Sheets("Export").Range(Cells(row + 1, 1), Cells(row + 1, 30)) = entry.ColumnValues
    row = row + 1
    Set entry = ve.GetNextEntry(entry)
Wend
 
Application.Calculate
   
For i = 2 To row - 1
    If Sheets("Export").Cells(i, 1) = "" Then
        i = row
    ElseIf Sheets("Export").Cells(i, 1) = Sheets("Export").Cells(i + 1, 1) Then
        Rows(i + 1).Delete
        i = i - 1
    End If
 
Next i
 
Rem Connect to Domino Database
Count = 1
 
' 1.1 Permanent
Sheets("Overdue").Range("A3:H100").ClearContents
Sheets("Overdue").Range("A3:H100").Font.Bold = False
Sheets("Overdue").Range("A3:H100").Interior.ColorIndex = xlNone
Sheets("Overdue").Range("A3:H100").Font.Size = 10
Sheets("Overdue").Range("A6:G6").Font.Bold = True
Sheets("Overdue").Range("A6:G6").Interior.ColorIndex = 15
Sheets("Overdue").Cells(Count + 4, 1).Font.Bold = True
Sheets("Overdue").Cells(Count + 2, 1).Font.Bold = True
Sheets("Overdue").Cells(Count + 2, 1).Font.Size = 12
Sheets("Overdue").Cells(Count + 2, 1) = "MODIFICATIONS DUE AND/OR OVERDUE TODAY"
Sheets("Overdue").Cells(Count + 4, 1) = "Permanent MODs"
Sheets("Overdue").Cells(Count + 5, 1) = "Mod#"
Sheets("Overdue").Cells(Count + 5, 2) = "Status"
Sheets("Overdue").Cells(Count + 5, 3) = "Title"
Sheets("Overdue").Cells(Count + 5, 4) = "Implementer"
Sheets("Overdue").Cells(Count + 5, 5) = "Commissioned Date"
Sheets("Overdue").Cells(Count + 5, 6) = "Due Date"
Sheets("Overdue").Cells(Count + 5, 7) = "Dept"
For i = 2 To row
    If Sheets("Export").Cells(i, 36) = "Yes" And Sheets("Export").Cells(i, 34) = "Mnt" Then
        'Mod number
        Sheets("Overdue").Cells(Count + 6, 1) = Sheets("Export").Cells(i, 1)
        'Status
        Sheets("Overdue").Cells(Count + 6, 2) = Sheets("Export").Cells(i, 2)
        'Title
        Sheets("Overdue").Cells(Count + 6, 3) = Sheets("Export").Cells(i, 5)
        'Implementor
        Sheets("Overdue").Cells(Count + 6, 4) = Sheets("Export").Cells(i, 16)
         'Commissioned Date
        Sheets("Overdue").Cells(Count + 6, 5) = Sheets("Export").Cells(i, 19)
         'Due Date
        Sheets("Overdue").Cells(Count + 6, 6) = Sheets("Export").Cells(i, 32)
         'Department
        Sheets("Overdue").Cells(Count + 6, 7) = Sheets("Export").Cells(i, 34)
        Count = Count + 1
    End If
Next i
 
' 1.2 Temporary for Removal
Sheets("Overdue").Cells(Count + 8, 1) = "Temporary MODs For Removal"
Sheets("Overdue").Cells(Count + 8, 1).Font.Bold = True
Sheets("Overdue").Cells(Count + 9, 1).Resize(, 8).Interior.ColorIndex = 15
Sheets("Overdue").Cells(Count + 9, 1).Resize(, 8).Font.Bold = True
Sheets("Overdue").Cells(Count + 9, 1) = "Mod#"
Sheets("Overdue").Cells(Count + 9, 2) = "Status"
Sheets("Overdue").Cells(Count + 9, 3) = "Title"
Sheets("Overdue").Cells(Count + 9, 4) = "Initiator"
Sheets("Overdue").Cells(Count + 9, 5) = "Implementer"
Sheets("Overdue").Cells(Count + 9, 6) = "Temp Mod Status"
Sheets("Overdue").Cells(Count + 9, 7) = "Temp Mod Removal Date"
Sheets("Overdue").Cells(Count + 9, 8) = "Dept"
For i = 2 To row
[I]    If Sheets("Export").Cells(i, 42) = "Yes" And Sheets("Export").Cells(i, 35) = "Mnt" Then    ‘’’’this is the line of code which returns the error[/I]
        'Mod number
        Sheets("Overdue").Cells(Count + 10, 1) = Sheets("Export").Cells(i, 1)
        'Status
        Sheets("Overdue").Cells(Count + 10, 2) = Sheets("Export").Cells(i, 2)
        'Title
        Sheets("Overdue").Cells(Count + 10, 3) = Sheets("Export").Cells(i, 5)
        'Initiator
        Sheets("Overdue").Cells(Count + 10, 4) = Sheets("Export").Cells(i, 6)
        'Implementor
        Sheets("Overdue").Cells(Count + 10, 5) = Sheets("Export").Cells(i, 16)
        'Temp Mod Status
        Sheets("Overdue").Cells(Count + 10, 6) = Sheets("Export").Cells(i, 27)
        'Removal Date
        Sheets("Overdue").Cells(Count + 10, 7) = Sheets("Export").Cells(i, 28)
        'Department
        Sheets("Overdue").Cells(Count + 10, 8) = Sheets("Export").Cells(i, 35)
        Count = Count + 1
    End If
Next i
 
Application.Calculation = xlCalculationAutomatic
Dim time2 As Date
time2 = Now()
 
MsgBox ("Export complete...Start:" & time1 & "  Finish:" & time2)
 
Rem "Clean up Notes objects"
Set session = Nothing
Set db = Nothing
Set ve = Nothing
Set view = Nothing
Set entry = Nothing
 
End Sub

 

[StephenCrump]

The Type Mismatch message suggests you have an error value (e.g. #N/A, or #NAME?)
in Sheets("Export").Cells(i, 42) and/or Sheets("Export").Cells(i, 35)

 

[Nadine67]

Oh my gosh really? You are so correct. Thank you so very much and I have to say I honestly did not even consider that to be the issue.

Thank you once again Stephen

 

[StephenCrump]

I have to say I honestly did not even consider that to be the issue.

Yes, it's not a particularly helpful error message the first time you encounter it, but I'm sure you'll quickly recognise the problem (i.e. error value --> type mismatch error message) next time it happens.

 

[Nadine67]

Oh I am sure I will not forget this one Stephen.

Thanks again!