vendredi 13 septembre 2019

Need help moving to the next entry in an array if the first IF is false?

I have a user tool where they need to put a customer's first name in one column and their last name in the next column. Many people just put the full name in the first column, causing us extra work.

I'm trying to write a sub to check if the first column has data and if so check if the second column does. If the first column has no data, fine, it's just empty. If the first does but the second doesn't, I want to throw up a messagebox and change a variable value to 1, then stop the function. IF both have data, fine, carry on and check the next cell in the column.

My current code (I think) first checks if the first cell is blank, and if it's not checks if the second cell is blank. If it is, it throws up an msgbox and fills a variable. This variable is checked once the loop is over (in a different module) to stop the follow-on processes if it's value is greater than 1.

I've tried adding an End If but that just stops it from working. I'm not sure where else to go.

    Dim nameRange() As Variant
    nameRange = Array("C10:C500") 'add all addresses here

    Dim cell As Variant
    For Each cell In nameRange
        If IsEmpty(Worksheets("Input").Range(cell)) = False Then
        If IsEmpty(Worksheets("Input").Range(cell).Offset(0, 1)) = True Then
        MsgBox "Please format names correctly."
        nameNet = 1
        Else
            Exit For
        End If
    Next cell
End Sub

I'm expecting it to check if the first cell is empty and if it's not, check the cell next to it. If that one is empty, it should display the messagebox and change the variable. If the second box also isn't empty, that's fine and it can move on to the next cell.

I get that currently, there's no instruction for what to do if the first cell is empty but I'm not sure how to put that in and I figured the Else takes care of that? If the first if is True, then it wouldn't do the second If and skip to the end?

Currently I'm getting 'Next without For' error which I'm guessing is related to the second If statement as I have a similar loop with a single If that works just fine.

Aucun commentaire:

Enregistrer un commentaire