excel - How to run a loop only once in vba

Question

Actually I'm making a code that will find the first blank cell in column 1 and input "1" if I enter "Hello" in cell B1. But the problem is when I input "hello" all the cell of column 1 gets filled with "1". Here is the code :

Private Sub Worksheet_Change(ByVal Target As Range)

Dim ws As Worksheet
Set ws = ActiveSheet

If Range("B1").Value = "Hello" Then

    For Each cell In ws.Columns(1).Cells
        If IsEmpty(cell) = True Then cell.Value = "1": Exit For
    Next cell
    
End If

End Sub

Actually I know the fault of my code. When I input hello in cell B1 The loop invokes. And as the loops find the input it executes infinitely. But I am not unable to make the loop execute only once. Pls help anyone!

Answer 1

One Cell Worksheet Change

• This will work only if you manually, or by using VBA, write "Hello" into cell B1.
• If you click into cell B1 and press Enter, the change will still be 'registered' although the value hasn't changed. If you would want to prevent this you could use a public variable to 'track' the change and probably 'involve' the Worksheet_Open event.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.CountLarge = 1 Then
        If Not Intersect(Range("B1"), Target) Is Nothing Then
            If Target.Value = "Hello" Then
                Dim cell As Range
                For Each cell In Columns(1).Cells
                    If IsEmpty(cell) Then
                        cell.Value = 1
                        Exit For
                    End If
                Next cell
           End If
        End If
    End If

End Sub