0

I have a range D15:V15 in which only one cell might contain a value. I would like to copy the cell that contains a value to the cell O7 each time there is a change on that sheet. This formula below shows the expected value but keeps crashing after displaying the value. Could someone please help me identify what may be the cause.

Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim SrchRng As Range, cel As Range
Set SrchRng = Range("D15:V15")
For Each cel In SrchRng
    If cel.Value <> "" Then
        'cel.Offset(-8, 11).Value = cel.Value
        Range("O7").Value = cel.Value
    End If
Next cel
braX
  • 11,506
  • 5
  • 20
  • 33
Nino Brown
  • 1
  • Yes it will crash. You may want to see [Why MS Excel crashes and closes during Worksheet_Change Sub procedure?](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure) – Siddharth Rout Feb 26 '20 at 04:23
  • 1
    Does this answer your question? [Why MS Excel crashes and closes during Worksheet\_Change Sub procedure?](https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure) – BigBen Feb 26 '20 at 04:24
  • I still have a crash. I have tried to insert the If Target.Cells.CountLarge > 1 Then Exit Sub but it keep crashing same as adding Application.EnableEvents = False. Any other way to solve this please. My knowledge on VBA is not so good. – Nino Brown Feb 26 '20 at 04:44

0 Answers0