CODEWITHSUNDEEP
excelvba
James
James

Reputation: 449

For Loop through Named Range cells names

This is the scenario:

if a checkbox (SamePObx) is selected, i want to loop through a named range and verify that there is at least one value present in the cells, but the cell that contains a value cannot be named "PO_Cmt". If all the cells are blank then a msgbox pops up.

    If Sheet1.SamePObx.Value = True Then 'if checkbox is selected
        For Each cell In Sheet1.Range("SamePO")
            'if the cell is blank and isn't name PO_Cmt
            If (cell.Value <> "") And (cell.Name <> "PO_Cmt") Then
                x = x + 1 'one PO is present
                Exit For
            End If
        Next cell

        'if no POs present, flag
         If x = 0 Then
             MsgBox "Please provide the necessary PO#(s)"
             GoTo cont
     End If

the issue i'm running into is a runtime error of 1004. this line is the issue:

If (cell.Value <> "") And (cell.Name <> "PO_Cmt") Then

Upvotes: 0

Views: 245

Answers (1)

Tim Williams
Tim Williams

Reputation: 166331

If Sheet1.SamePObx.Value = True Then 'if checkbox is selected

    x = Application.CountA(Sheet1.Range("SamePO"))

    If Range("PO_Cmt").Value <> "" Then x = x - 1

    'if no POs present, flag
     If x = 0 Then
         MsgBox "Please provide the necessary PO#(s)"
         GoTo cont
     End If
 End If

Upvotes: 1

Related Questions