Worksheet change VBA

Question

I've been working Worksheet_Change VBA code in Excel as shown below.

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:A2000")) Is Nothing Then Call writetag End If End Sub

Sub writetag() ActiveCell.Select ActiveCell.Offset(0, 1).Select ActiveCell.Formula = "1st Request" End Sub

The writetag VBA code alone does its job just fine wherein it would move 1 cell to the right of the active cell and write "1st Request". Then I have the first VBA code - Worksheet_Change - that will trigger the writetag code whenever there are changes made on range A2:A2000. But it is at this part that the writetag code does not work perfectly. Instead of moving 1 cell to the right, it would move 1 cell to the right and another 1 cell below. So I need to adjust ActiveCell.Offset(0, 1).Select to ActiveCell.Offset(-1, 1).Select just so that it would move to the right cell. Then after that, I would like to make 3 conditions or Ifs, wherein when I put 1 anywhere on the A2:A2000 range, it will put "1st Request" to its right. If I put 2 anywhere on the range, it will put "2nd Request" to its right, "3rd Request" if I put 3.

Thank you so much for the help.

Also worth a READ since you are working with Worksheet_Change — Siddharth Rout
– Siddharth Rout, Commented Jan 13, 2020 at 10:21

Dang D. Khanh · Accepted Answer · 2020-01-13 05:52:47Z

Remember target is the address of the called cell, in your case suppose you entered in cell A1:

target = [A1] but the problem is that this event fires after the value is changed so after press enter ActiveCell = [A2], then the event is called and the result is

ActiveCell.offset (0,1) = [A2] .offset (0,1) = [B2]

so your code is not working, let's try:

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:A2000")) Is Nothing Then Call writetag(Target) End If End Sub Sub writetag(rng As Range) With rng.Offset(0, 1) Select Case rng.Value2 Case 1 .Formula = "1st Request" Case 2 .Formula = "2nd Request" Case 3 .Formula = "3rd Request" End Select End With End Sub

Thank you. This worked perfectly well! I'm so grateful for the quick and elegant solution.

Harun24hr · Accepted Answer · 2020-01-13 05:29:44Z

Use following sub when you enter value and press ENTER from keyboard.

Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A2:A2000")) Is Nothing Then Call writetag End If End Sub

Sub writetag() Dim curr As Variant curr = ActiveCell.Offset(-1, 0) ActiveCell.Offset(-1, 1) = curr & "st Request" End Sub

Thank you. This worked well, except for the "st Request" part since it would not fit when I need "2nd Request", "3rd Request", etc to fill. But this idea definitely will be useful to some of my projects.

Collectives™ on Stack Overflow

Worksheet change VBA

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related