CODEWITHSUNDEEP
excelvba
James
James

Reputation: 449

For Loop Being Skipped Over

I have not been able to figure out why the For Loop block (with two for loops) is being skipped over by the code. I know it's being skipped bc a message box is being tripped further down in the code. Any thoughts?

Dim OL As Outlook.Application
Dim myitem As Outlook.MailItem
Dim wb As Workbook
Dim ws As Worksheet
Dim PicPath As String
Dim x, y As Integer
Dim Name As Name
Dim cell, rng1 As Range


Set wb = ActiveWorkbook
Set ws = wb.Sheets("Sheet1")

wb.Sheets("Sheet1").Activate

'Request type check
If (Sheet1.NewResource.Value = False) And (Sheet1.Modification.Value = False) Then
    MsgBox "Please select a Request Type of your request and complete the other mandatory fields"
    GoTo cont
End If

'checking New Resource Entry mandatory fields
If Sheet1.NewResource.Value = True Then

    If (Sheet1.NewPOnobx.Value = False) And (Sheet1.NewPOyesbx.Value = False) Then
        MsgBox "Please fill the missing mandatory fields"
        GoTo cont
    End If

    If (Sheet1.POyesbx.Value = False) And (Sheet1.POnobx.Value = False) Then
        MsgBox "Please fill the missing mandatory fields"
        GoTo cont
    End If

    If (Sheet1.POnobx.Value = True) And (Sheet1.SOWtxt.Value = "") Then
        MsgBox "Please select a copy of the Signed Agreement"
        GoTo cont
    End If

    For Each Name In ActiveWorkbook.Names 'each named range in gateway fields
        If (Name = "C_LN") Or (Name = "C_FN") Or (Name = "C_SD") Or (Name =  "C_ED") Or (Name = "C_O") Or (Name = "C_D") Or (Name = "C_OF") Or (Name = "C_C") Or (Name = "C_M") Or _
        (Name = "C_R") Or (Name = "C_PT") Or (Name = "C_V") Or (Name = "C_SAR") Or (Name = "C_BAR") Or (Name = "C_SR") Or (Name = "C_SS") Or (Name = "C_PO") Then 'all gateway field names
            x = 0
            For Each cell In Name 'cell in the named range
                If cell.Value = "" Then 'looking for blank mandatory fields
                    If (cell.Name = "LN_Cmt") Or (cell.Name = "FN_Cmt") Or (cell.Name = "SD_Cmt") Or (cell.Name = "ED_Cmt") Or (cell.Name = "O_Cmt") Or _
                    (cell.Name = "D_Cmt") Or (cell.Name = "OF_Cmt") Or (cell.Name = "C_Cmt") Or (cell.Name = "M_Cmt") Or (cell.Name = "R_Cmt") Or _
                    (cell.Name = "PT_Cmt") Or (cell.Name = "V_Cmt") Or (cell.Name = "SAR_Cmt") Or (cell.Name = "BAR_Cmt") Or (cell.Name = "SR_Cmt") Or _
                    (cell.Name = "SS_Cmt") Or (cell.Name = "PO_Cmt") Then
                        GoTo skip1
                    Else
                        x = x + 1 '+1 for a blank mandatory field
                    End If
                End If
skip1:
            Next cell

                If x > 0 Then 'flag the missing info
                    MsgBox "Please fill the missing mandatory fields"
                    GoTo cont
                End If

            End If
        Next Name

End If

Upvotes: 0

Views: 109

Answers (2)

Captain
Captain

Reputation: 2238

For me, the Name in the for loop gives something like =Sheet1!$G$2:$G$5 ... this doesn't match your C_R or whatever. I need to use Name.Name to get that. And then to get the range of cells for the name, I need to use Name.RefersToRange.

Also, to make your If list easier to manage, you could use Select Case instead:

  Select Case Name.Name
    Case "C_LN", "C_FN", "C_SD",  "C_ED", "C_O", "C_D", "C_OF", "C_C", "C_M", "C_R", "C_PT", "C_V", "C_SAR", "C_BAR", "C_SR", "C_SS", "C_PO"
      For Each cell In Name.RefersToRange
        Debug.Print cell.Address
      Next cell
    Case Else
      Debug.Print "Other Name"
  End Select

Upvotes: 1

Dan Donoghue
Dan Donoghue

Reputation: 6216

Don't use name it is a reserved word (you can rename files with Name File1 as File2).

Change the variable name to MyName or something similar then if you continue to experience the issue, put a stop on the code and evaluate the loop manually in the debug wind (CTRL-G) by typing ?activeworkbook.Names.Count and pressing enter (don't forget the ?) and see if you have a number above zero.

Upvotes: 0

Related Questions