'Next without for' compile error

[KristenB]

Hello - I have the following macro and it is hanging up with a "next without for" compile error. I don't see what is wrong with it? Could someone please help me? It is hanging up on the line 'Next mycell".

Public Sub ProjNumbrReq()
'Call ProjNumbrReq
Dim myCell As Range
With Worksheets("Travel Expense Voucher")
  If .Range("F5") = 2 Then
    For Each myCell In .Range("W16:W46")
        If myCell.Value > 0 And .Cells(myCell.row, "N") = "" Then
          MsgBox "Project Number must be provided on each line where reimbursement is being claimed.", vbCritical, "Important:"
          Exit Sub
     Next myCell
  End If
 End With
End Sub

 

[lrobbo314]

You were missing an 'end if'. This is why indentation is your friend.

Public Sub ProjNumbrReq()
'Call ProjNumbrReq
Dim myCell As Range
With Worksheets("Travel Expense Voucher")
    If .Range("F5") = 2 Then
        For Each myCell In .Range("W16:W46")
            If myCell.Value > 0 And .Cells(myCell.Row, "N") = "" Then
                MsgBox "Project Number must be provided on each line where reimbursement is being claimed.", vbCritical, "Important:"
                Exit Sub
            End If
        Next myCell
    End If
End With
End Sub

 

[KristenB]

I updated and now I'm getting a 'Runtime Error 13 Type Mismatch" This particular code is going to give me serious gray hair!

 

[lrobbo314]

What line is the error being thrown on?

 

[DanteAmor]

In the range of W16:W46 cells do you have formulas? Do any of these formulas return error?

 

[KristenB]

You know what? I am working on a few other formulas on that worksheet and some of the cells in that range have a #value error on them. Will that cause the error that I'm getting? I'm new to this coding thing so please forgive me if that is a stupid question.

 

[DanteAmor]

Do not worry, we are all learning.
Yes, the error in the cell is the cause of the error in the code, but you can try the following:

Public Sub ProjNumbrReq()
'Call ProjNumbrReq
  Dim myCell As Range
  With Worksheets("Travel Expense Voucher")
    If .Range("F5") = 2 Then
        For Each myCell In .Range("W16:W46")
          If Not IsError(myCell) Then
            If myCell.Value > 0 And .Cells(myCell.Row, "N") = "" Then
                MsgBox "Project Number must be provided on each line where reimbursement is being claimed.", vbCritical, "Important:"
                Exit Sub
            End If
          End If
        Next myCell
    End If
  End With
End Sub

 

[KristenB]

Perfect!!! Thank you! Now if I can just figure out the rest of this. I'm also trying to add userforms and some of that coding is challenging. Additionally, I'm also trying to figure out how to calculate time, determining if there was at least 3 hours between times, to create errors when someone is trying to claim a reimbursement for a time frame that is not allowed. Sound fun? Thank you so much for your help!!

 

[alansidman]

crossposted: https://chandoo.org/forum/threads/syntax-error.43013/#post-256516

 

[DanteAmor]

Im glad to help you. Thanks for the feedback.