VBA Error in Worksheet Change code

[Mojo689]

Good Morning. I'm having trouble with the above code.

I have two separate tables, but the code needs to stay generic and will be entered into each worksheet that will be using it. The first table requires the user to update certain fields and then choses the value in column M as either Yes or No. If no is chosen, nothing runs and the user continues entering. If Yes is chosen, the above code is to run. When it runs, it copies the values in that same row from columns C and D into the next available row in column P (and Q), which is in the other table. I don't have the code in here yet, but once the values are copied into column P, the code is supposed to select the next cell beside the copied values (column R), stop, and then allow the user to fill in any information in the remaining columns in that row. After that, the user will go back to the first table and continue entering/chosing M column values, and the code process starts again if relevant.

However, once the code is done initially (and it does copy everything as required), an error code comes up and the highlighted If statement is apparently the problem - it's not ending and looks like it's stuck in a loop or something - I'm guessing because now the column has more than 0 'yes' value; I'm really not sure what's going on. Can anyone help me set this code up properly?

Thanks very much for any help you can give.

 

[offthelip]

The highlighted line is incorrect it should be

If Instr(1,Target,"Yes") > 0 then

if you want it to detect "Yes" in the cell

 

[joeu2004]

You have a misplaced End If statement. The form of the code should be:

If Not Intersect(...) Is Nothing Then
    If Instr(...) Then
        r2.Copy ...
    End If
End If

In the future, please copy VBA text and paste between "code" tags so that __we__ can copy-and-paste with corrections.

PS.... Possible secondary error: your code assumes that Target.Count = 1. You should probably confirm that condition in your code.

PPS.... There is no difference between Target and Range(Target.Address), as demonstrated below. So of course, using just Target is better.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Boolean, y As Boolean, zz As Boolean
If Not Intersect(Target, Range("a1")) Is Nothing And Target.Count = 1 Then
    If InStr(1, Target, "yes") > 0 Then x = True
    If InStr(1, Range(Target.Address), "yes") > 0 Then y = True
    zz = (Not Intersect(Target, Range(Target.Address)) Is Nothing)
    MsgBox x & "    " & y & "    " & zz
End If
End Sub

The MsgBox displays "True True True" when A1 is changed to contain the string "abcyesxyz" (or any string with "yes" after position 1).

 

[joeu2004]

Errata (too late to edit)....

The MsgBox displays "True True True" when A1 is changed to contain the string "abcyesxyz" (or any string with "yes" after position 1).

"In or after" position 1. Basically, "after position 1" is a superfluous condition. Klunk!

 

[Mojo689]

Thanks very much for your replies. I've tried all your suggestions and ended up with the code below. I am testing on only 2 of the worksheets - there are 14 individual user worksheets that will house this code. There is no protection on any of the sheets.

It works perfectly in one worksheet; as soon as "Yes" is chosen from the dropdown menu, the corresponding information from column C and D are copied and pasted into a separate table on the same sheet in columns P and Q. The next time that person selects "Yes", column C and D from that row are copied and pasted into the next available row in column P, and so on.

I have the same exact code in the other worksheet but it doesn't work properly. It will paste the relevant data into column P but then when I choose "Yes" in another row in column M, it copies it but overwrites the previous pasting job. So frustrating

The other thing I wanted it to do was this: once the information is pasted, I wanted the user to immediately be taken to that same pasted row and placed in column R so that they can fill out the rest of the row with required information. From there, they can go back to the first table and begin the process again. I tried pointing to the row ( that's what "nextC" in the original post was for), but it always took me to last row in the table. I gave up and just took it out of the code.

I am completely lost as to how to get this to work properly for all user worksheets, and I need this to work as we have some users who 'need the guidance' on what to do next (to ensure they fill out all the required fields). Help?!

Private Sub Worksheet_Change(ByVal Target As Range)

Dim r2 As Range, lastR As Long, nextC As Long

Set r2 = Range(ActiveCell.Offset(0, -10), ActiveCell.Offset(0, -9))
lastR = Range("P4").End(xlDown).Row + 1
nextC = Range("R4").End(xlDown).Row + 1


If Not Intersect(Target, Range("M5", Range("M" & Rows.Count).End(xlUp))) Is Nothing And Target.Count >= 1 Then
    Application.EnableEvents = False
    If InStr(1, Target, "Yes") > 0 Then
    
    r2.Copy Range("P" & lastR)
    End If
End If

Application.CutCopyMode = False

Application.EnableEvents = True


End Sub