0

I have a problem with VBA, I need to use the worksheet change event to pickup cell values from AI28 to AI30 and move them over to V28 to V30. This is what I have do so far

Private Sub Worksheet_Change(ByVal Target As Range)

If IsNumeric(Target) And Not (Target = "") Then
  If Target.Address = Range("AI28:AI30").Address Then
   Range("V28:V30").Value = Range("AH28:AH30").Value
   
   Else
      If Target.Cells.Value <> Empty Then Exit Sub
   Exit Sub
   
   End If

End If
    
End Sub

It works fine for just one range eg AI28 and V28 so what am I missing? A loop or something?

BigBen
  • 46,229
  • 7
  • 24
  • 40
ChrisR
  • 81
  • 9
  • So, if any cell is edited in AI28:AI30, the whole range AH28:AH30 should be copied to V28:V30? – BigBen Jun 24 '21 at 18:18
  • Sorry no, If I change AI28 to 10, AI29 to 20 and AI30 to 30 those values should be copied over to V28 ,29 and 30 – ChrisR Jun 24 '21 at 18:21
  • Try using `Intersect(R1, R2)` to test if the `Target` is in `Range("AI28:AI30")` or not. – newacc2240 Jun 24 '21 at 18:22
  • more info https://stackoverflow.com/questions/36547636/how-to-find-if-a-range-is-included-in-another-range-using-vba – newacc2240 Jun 24 '21 at 18:23
  • https://stackoverflow.com/questions/13860894/why-ms-excel-crashes-and-closes-during-worksheet-change-sub-procedure/13861640#13861640 – Siddharth Rout Jun 24 '21 at 18:35

1 Answers1

1

Use a loop and Intersect:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Target, Me.Range("AI28:AI30"))

    If rng Is Nothing Then Exit Sub

    On Error GoTo SafeExit
    Application.EnableEvents = False

    Dim cell As Range
    For Each cell In rng
        If IsNumeric(cell.Value) And Not IsEmpty(cell.Value) Then
           Me.Range("V" & cell.Row).Value = cell.Value
        End If
    Next

SafeExit:
    Application.EnableEvents = True

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40