If condition in a loop is not met, show a msg box

Rellic 0
Viewed 40

I'm am trying to find out how I can show a messagebox if the first if statement is not met. If you delete the else statement it does what it should do, put the values in the correct cells. But if the else statement is added it will show the messagebox immediately.

Sub Column_Names2()
Dim c
For Each c In Range("A1:Z1").Cells
    If c = "" Then
        c.Value = "Accounting Number"
        c.Offset(0, 1).Value = "Receipt/Invoice"
        c.Offset(0, 2).Value = "Proccesed"
    Else: MsgBox "Could not find empty cell"
        Exit For
    End If
Next
End Sub
vba excel
2 Answers

So as you said in the comments, you have blank stills first (meaning to the left). You are also exiting the loop after this else condition is met. Ultimately you need to finish the loop before you want to say none have been found.

So try something like this. I didn't test but I suspect it will work for you.

Sub Column_Names2()
    Const rangeOfCells = "A1:Z1"
    Dim c As Range, foundBlankCell As Boolean
    
    For Each c In Range(rangeOfCells).Cells
        If c.Value = "" Then
            foundBlankCell = True
            c.Value = "Accounting Number"
            c.Offset(0, 1).Value = "Receipt/Invoice"
            c.Offset(0, 2).Value = "Proccesed"
            Exit For '<--- assuming you want the check of columns to end
        End If
    Next
    
    If Not foundBlankCell Then MsgBox "Could not find empty cell"
End Sub
pgSystemTester0

probably use it like this

Sub Column_Names2()
Dim c
For Each c In Range("A1:Z1").Cells
    If c = "" Then
        c.Value = "Accounting Number"
        c.Offset(0, 1).Value = "Receipt/Invoice"
        c.Offset(0, 2).Value = "Proccesed"
    ElseIF c <> "" Then 'just inverse your other condition
     MsgBox "Could not find empty cell"
        Exit For
    End If
Next
End Sub
Brugon0
Related