WorksheetChange Event triggers 47 times?

[KKaren]

Hello,

I am a newbie in VBA and I have a question regarding the Worksheet_Change event. Let's say, anytime something changes in the worksheet, I want to write something in the next empty cell in column A. So I have the following simple code:

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Range("A" & Rows.Count).End(xlUp).Value = "" Then
        Range("A" & Rows.Count).End(xlUp).Value = "Beer"
    Else
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Beer"
    End If
    
End Sub

In the beginning, all cells in column A are empty, so I when I trigger this event, all cells from A1 to A47 have the value of "Beer". When I trigger it one more time, all cells to A94 have the value of "Beer".

Obviously, when Excel updates cell A1 with "Beer", this triggers the event once again and then it executes the part in the Else block and this process is being repeated, but my question is why exactly 47 times?!

I will be glad if somebody answers as I have tried searching here and at other forums as well but without any luck.

Thanks in advance and cheers

 

[KKaren]

Hello,

No, I don't get any error.

In fact I can trigger the event once again and this time all cells from A48 to A94 (again 47 rows) have the value of "Beer"...

 

[Norie]

Is there anything else going on?

Other event code? Data on the sheet?

 

[KKaren]

Is there anything else going on?

Other event code? Data on the sheet?

No, I am using a blank workbook. And this is the only code I have in it.

 

[wideboydixon]

I'm pretty confident it a stack error that's stopping execution but I've no idea what's "swallowing" the error so that you don't see it.

WBD

 

[Norie]

When I try this code I eventually (well quite quickly actually) get an 'out of stack space error' and the last value printed to the debug window is 76i which suggests 76ish iterations of the code.

Private Sub Worksheet_Change(ByVal Target As Range)
Static I As Long

    I = I + 1
    Debug.Print I
    
    If Range("A" & Rows.Count).End(xlUp).Value = "" Then
        Range("A" & Rows.Count).End(xlUp).Value = "Beer"
    Else
        Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = "Beer"
    End If
    
End Sub

 

[KKaren]

Probably it depends on the PC then, or else I have no idea what might be the issue.

Thank you for looking into this.

 

[My Aswer Is This]

I think you need something like this:
If any value on the sheet changes not including Column A will cause the value "Beer" to be put in column "A" One after the other.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 1 Then
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(lastrow, 1).Value = "Beer"
End If
End Sub

 

[My Aswer Is This]

Now this script will put the value you just entered into the cell into column A

Private Sub Worksheet_Change(ByVal Target As Range)
'Target.value
If Target.Column <> 1 Then
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(lastrow, 1).Value = Target.Value
End If
End Sub

 

[KKaren]

Hi,

Well actually I was curious about as to why this is repeated exaclty 47 times, and I had even written another post below the posts of @Joe4 and @mikerickson to thank them for their answers and to clarify my question, but somebody must have deleted it as I don't see it now.

Thanks

 

[My Aswer Is This]

Hi,

Well actually I was curious about as to why this is repeated exaclty 47 times, and I had even written another post below the posts of @Joe4 and @mikerickson to thank them for their answers and to clarify my question, but somebody must have deleted it as I don't see it now.

Thanks

I would say your in a continuous loop. Now why it stops after 27 times I have no answer. Your changing a cell value then telling the script to change a cell because you changed a cell value.