Run time error 13 Type mismatch

[bradbeatDTD]

Hi

You'll probably realise I'm very new to vba, so any help would be much appreciated.
Some of the cells in the excel being evaluated contain #VALUE! and this is causing the run time error in the if statement below



For n = 2 To 50


If Sheets(1).Cells(n, 7) = "Complete" And Sheets(1).Cells(n, 5) > Now - 30 Then
ProjectName = Sheets(1).Cells(n, 1)
MilestoneName = Sheets(1).Cells(n, 2)
Owner = Sheets(1).Cells(n, 3)
ActualDate = Sheets(1).Cells(49, 5)
Status = Sheets(1).Cells(n, 7)
Sheets(2).Cells(3, 1) = ProjectName
Sheets(2).Cells(3, 2) = MilestoneName
Sheets(2).Cells(3, 3) = Owner
Sheets(2).Cells(3, 4) = ActualDate
Sheets(2).Cells(3, 5) = Status
End If


Next n

[mancubus]

welcome to the forum.

upload a sample file to work with. (see my signature)
make sure it contains the data to reproduce the rte 13.

adding "On Error Resume Next" will suppress the alerts. but be very careful when using it. (giybf here.)

other than the error, that bit of the code will continuously overwrite the values that meet the two conditions from sheet1 to row 3 of sheet2 (A3:E3) . is it what you want?

you can rewrite your code as follows (bur i2m not sure it will do what you really want):


Sub test()

On Error Resume Next

With Sheets(1)
For n = 2 To 50
If .Cells(n, 7) = "Complete" And .Cells(n, 5) > Now - 30 Then
Sheets(2).Cells(3, 1) = .Cells(n, 1)
Sheets(2).Cells(3, 2) = .Cells(n, 2)
Sheets(2).Cells(3, 3) = .Cells(n, 3)
Sheets(2).Cells(3, 4) = .Cells(49, 5)
Sheets(2).Cells(3, 5) = .Cells(n, 7)
End If
Next n
End With

End Sub

[mdmackillop]

You can post a sample workbook using Go Advanced / Manage Attachments

Your code is overwriting the same cells every time. Only the highest value of n which gives true for line 2 will be shown.

[Paul_Hossler]

Not sure what your overall intention is, but here's a couple of thoughts




Option Explicit

Sub one()

Dim n As Long
For n = 2 To 50

'personal style to have If ... Then GoTo ...
' but I find it more readable that a lot of compound and/or nested If statements
If IsError(Sheets(1).Cells(n, 7).Value) Then GoTo GetNext
If IsError(Sheets(1).Cells(n, 5).Value) Then GoTo GetNext

If Sheets(1).Cells(n, 7) <> "Complete" Then GoTo GetNext
If Sheets(1).Cells(n, 5) <= Now - 30 Then GoTo GetNext

ProjectName = Sheets(1).Cells(n, 1)
MilestoneName = Sheets(1).Cells(n, 2)
Owner = Sheets(1).Cells(n, 3)
ActualDate = Sheets(1).Cells(49, 5)
Status = Sheets(1).Cells(n, 7)

' since row 3 is ALWAYS used, the last one in wins
Sheets(2).Cells(3, 1) = ProjectName
Sheets(2).Cells(3, 2) = MilestoneName
Sheets(2).Cells(3, 3) = Owner
Sheets(2).Cells(3, 4) = ActualDate
Sheets(2).Cells(3, 5) = Status

'or skipping the middle man

Sheets(2).Cells(3, 1) = Sheets(1).Cells(n, 1)
Sheets(2).Cells(3, 2) = Sheets(1).Cells(n, 2)
Sheets(2).Cells(3, 3) = Sheets(1).Cells(n, 3)
Sheets(2).Cells(3, 4) = Sheets(1).Cells(n, 5)
Sheets(2).Cells(3, 5) = Sheets(1).Cells(n, 7)


GetNext:

Next n
End Sub

[bradbeatDTD]

Thanks Paul. I like your style! and solved the issue with the IsError statement skipping over cells containing #VALUE!