Macro for change in cells

[Infinity]

I was reading a thread "Next row". Would appreciate if someone could tell me how do i do it for cells A1 to A3. I tried inserting "Target = Worksheets("sheet1").Range("A1:A3"))" but it give me an error message.

Thanks in advance....

Below is the code used in the thread "Next row".

Sub Worksheet_Change(ByVal Target As Range)
   'only snap those changes occurred at A1 in sheet1, while others omitted
   If (Target = Worksheets("sheet1").Range("A1")) Then
      Dim iRow As Integer 'start row for sheet2
      Dim iCol As Integer 'start col for sheet2
      iCol = 1      
      iRow = Worksheets("sheet2").Cells(Rows.Count, iCol).End(xlUp).Row
      If iRow > 1 Then iRow = iRow + 1

      Worksheets("sheet2").Cells(iRow, iCol).Value = Target.Value
   End If
End Sub 

[Leith Ross]

Hello Infinity,

Since you don't say which error you are getting, I am assuming from the code that it is a cascade event failure. This happens when a event fires, and the code within the event causes the event to fire again, and so on. This creates a stack overflow, and and error is generated. Try this amended macro code...

Sub Worksheet_Change(ByVal Target As Range)
   'only snap those changes occurred at A1 in sheet1, while others omitted
 Application.EnableEvents = False
   If (Target = Worksheets("sheet1").Range("A1")) Then
      Dim iRow As Integer 'start row for sheet2
      Dim iCol As Integer 'start col for sheet2
      iCol = 1      
      iRow = Worksheets("sheet2").Cells(Rows.Count, iCol).End(xlUp).Row
      If iRow > 1 Then iRow = iRow + 1

      Worksheets("sheet2").Cells(iRow, iCol).Value = Target.Value
   End If
 Application.EnableEvents = True

End Sub

Sincerely,
Leith Ross

[Infinity]

This is what i did and it gave me run time error 13

Sorry couldnt use the php... couldnt highlight my diff.

Sub Worksheet_Change(ByVal Target As Range)
'only snap those changes occurred at A1 in sheet1, while others omitted
If (Target = Worksheets("sheet1").Range("A1:A3")) Then
Dim iRow As Integer 'start row for sheet2
Dim iCol As Integer 'start col for sheet2
iCol = 1
iRow = Worksheets("sheet2").Cells(Rows.Count, iCol).End(xlUp).Row
If iRow > 1 Then iRow = iRow + 1

Worksheets("sheet2").Cells(iRow, iCol).Value = Target.Value
End If
End Sub

[Leith Ross]

Hello Infinity,

Error code 13 is a "Type mismatch" error. The code is trying to check if the Target value is equal to the value of cells A1 to A3. The Value property doesn't return an array of values. This is what causes the mismatch error. If you want to restrict the user selection to cells A1 to A3 then the code needs to be as follows...

Sub Worksheet_Change(ByVal Target As Range)
  Dim iRow As Integer 'start row for sheet2
  Dim iCol As Integer 'start col for sheet2  
 'only snap those changes occurred at A1 to A3 on  the ActiveSheet, while others omitted

 Application.EnableEvents = False
   If Not Intersect(Target, Range("A1:A3")) Is Nothing Then
      iCol = 1      
      iRow = Worksheets("sheet2").Cells(Rows.Count, iCol).End(xlUp).Row
      If iRow > 1 Then iRow = iRow + 1
      Worksheets("sheet2").Cells(iRow, iCol).Value = Target.Value
   End If
 Application.EnableEvents = True

End Sub

Sincerely,
Leith Ross