Need assistance with VBA code

[deftincu]

Hi All,

Looking for some assistance, as I’ve tried several different things and have ended up with error messages each time.

I’m currently using the below VBA code, which inputs time stamps and names into adjacent cells. It is driven by specific statuses that are chosen from a drop down list, and I need to make a minor change to the highlighted section of the code.

In the highlighted section, it’s pulling the name from a status that is titled “On Hold - (then a persons name)”. The code extracts the name after the “-“ and inputs it into column 15. Instead of it pulling from that particular status, I now need it to pull from a status that will be titled “New - (persons name) to Review”. Each status will have a different name. Examples; “New - Chris to Review”, “New - Joe to Review”, etc. I would like it to only extract the name, not the “New -“ or “to Review”, and input it into column 15.

Aside from this small portion of the code, I would like to keep everything else functioning as it currently does. As mentioned, I’ve made a few attempts to change the code but keep receiving error messages, so any help will be greatly appreciated.

Thank you!

 

[6StringJazzer]

To show code, I suggest you copy the code from the editor and paste it directly into a post. We can't try running it from a screenshot. After pasting, select the code and click the VBA button to maintain formatting.

 

[kvsrinivasamurthy]

Try this.

Target.Offset(0, 15).Value = Trim(Split(Split(Target.Value, "-")(1), "to")(0))

 

[Peter_SSs]

IF the examples are as uniform as the two you have given, then this should do it

If InStr(1, Target.Value, "-") > 0 Then
  Target.Offset(0, 15).Value = Trim(Split(Target.Value)(2))
End If

Further to what @6StringJazzer has said about posting vba code, my signature block below has further information & link.

 

[deftincu]

To show code, I suggest you copy the code from the editor and paste it directly into a post. We can't try running it from a screenshot. After pasting, select the code and click the VBA button to maintain formatting.

Thank you for the tip! I’ll be sure to use it moving forward.

 

[Peter_SSs]

Thank you for the tip! I’ll be sure to use it moving forward.

No comment about whether the other suggestions did what you wanted?

Looking at your code again, you may also run into some issues because your code, under some circumstances, makes changes to the worksheet. This will re-trigger the Worksheet_Change code. It would be best to stop that happening and can easily be achieved by modifying the code.

There is also confusion in my mind because the code will only get to that highlighted code in post 1 if this is True ..

.. yet the description in post 1 seems to me to be saying that the values will not have "On Hold" in them at all.

Perhaps I am not understanding you data either so if you still need help with this, some sample for column Z (showing any variety that might occur) with XL2BB that we can use for testing and further explanation with reference to that sample data should help.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Of course your actual code in a form that we can copy, as described above, would also enable people to help you better.

 

[deftincu]

No comment about whether the other suggestions did what you wanted? Looking at your code again, you may also run into some issues because your code, under some circumstances, makes changes to the worksheet. This will re-trigger the Worksheet_Change code. It would be best to stop that happening and can easily be achieved by modifying the code. There is also confusion in my mind because the code will only get to that highlighted code in post 1 if this is True .. View attachment 120282 .. yet the description in post 1 seems to me to be saying that the values will not have "On Hold" in them at all. Perhaps I am not understanding you data either so if you still need help with this, some sample for column Z (showing any variety that might occur) with XL2BB that we can use for testing and further explanation with reference to that sample data should help. (If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.) Of course your actual code in a form that we can copy, as described above, would also enable people to help you better.

No comment about whether the other suggestions did what you wanted?

Looking at your code again, you may also run into some issues because your code, under some circumstances, makes changes to the worksheet. This will re-trigger the Worksheet_Change code. It would be best to stop that happening and can easily be achieved by modifying the code.

There is also confusion in my mind because the code will only get to that highlighted code in post 1 if this is True ..

View attachment 120282

.. yet the description in post 1 seems to me to be saying that the values will not have "On Hold" in them at all.

Perhaps I am not understanding you data either so if you still need help with this, some sample for column Z (showing any variety that might occur) with XL2BB that we can use for testing and further explanation with reference to that sample data should help.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Of course your actual code in a form that we can copy, as described above, would also enable people to help you better.

Apologies, I didn’t have a chance to test out your suggestion until today. Unfortunately, it did not work.

Hopefully the code comes out properly, below. I’m a bit limited when it comes to providing sample data, as I’m using an iPhone to respond to the messages and am unable to access this site on my work laptop, so I will do my best to describe what the code currently performs and what I need to change.

(A little background) Column Z in the data contains a drop down list that has many different statuses. Amongst those statuses are “On Hold - person’s name” or “On Hold - Person’s name (internal reason)”, “Order Assigned - person’s name”, and “Ready to Assign”. The code performs 5 different tasks based these statuses:

1. When selecting the status “On Hold - person’s name” or “On Hold - Person’s name (internal reason)” in column Z, a time stamp is entered into the adjacent cell in column AB and simultaneously it extracts that persons name from that status and enters it in the adjacent cell in column AN.
2. When selecting the status “Assigned - person’s name” in column Z, a time stamp is entered into an adjacent cell in column AJ and simultaneously extracts the person’s name from that status and enters it into the adjacent cell in column AO.
3. When selecting the status “Ready to Assign” in column Z, it enters a timestamp into an adjacent cell in column AI.
4. When selecting the status “Reset Hold Date”, it deletes out the timestamp in column AB.
5. When selecting the status “Reset Assigned Date”, it deletes out the timestamp in column AJ.

Currently, when each of the above tasks are performed, the timestamp and names are not removed if the status is changed again - it needs to remain that way, as the status can be updated many times after those initial tasks.

With that said, what I need to change is task 1. I would like for the timestamp to still be entered into column AB when “On Hold - person’s name” or “On Hold - Person’s name (internal reason)” is selected; however, I need the name that is entered into column AN to now be initiated when a different, new, status is selected. The status will be, “New - person’s name to review”. As mentioned in my initial post, I would like to ensure that the name that is extracted from that status does not include the words “New -“ or “to review” in it. The name also needs to remain intact if the status is changed again.

I hope this helps, and I really appreciate your assistance!

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Target, Me.Range("Z:Z")) Is Nothing Then

If Target.Count > 1 Then Exit Sub

Select Case True

Case InStr(1, Target.Value, "On Hold", vbTextCompare) > 0

With Target.Offset(0, 2)

If .Value = "" Then

.Value = Format(Date, "mm/dd/yyyy")

.NumberFormat = "mm/dd/yyyy"

End If



End With

If InStr(1, Target.Value, "-") > 0 Then

Target.Offset(0, 14).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))



End If



Case Left(Target.Value, 8) = "Assigned"

With Target.Offset(0, 10)

If .Value = "" Then

.Value = Format(Date, "mm/dd/yyyy")

If InStr(1, Target.Value, "-") > 0 Then

Target.Offset(0, 15).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))

End If

End If

End With



Case Left(Target.Value, 15) = "Ready to Assign"

With Target.Offset(0, 9)

If .Value = "" Then

.Value = Format(Date, "mm/dd/yyyy")

End If

End With



Case Target.Value = "Reset Hold Date"

Target.Offset(0, 2).Value = ""



Case Target.Value = "Reset Assigned Date"

Target.Offset(0, 10).Value = ""



End Select

End If

End Sub

 

[Peter_SSs]

Hopefully the code comes out properly, below.

Better, but still better if the code is indented & not spaced out quite so much, though that may have been affected by your attempts? There is a Test Here forum where you can try out things like that without interfering in a live thread.

I'm not sure if I have fully understood your descriptions, but see if this is getting closer.

however, I need the name that is entered into column AN to now be initiated when a different, new, status is selected. The status will be, “New - person’s name to review”. As mentioned in my initial post, I would like to ensure that the name that is extracted from that status does not include the words “New -“ or “to review” in it. The name also needs to remain intact if the status is changed again.

In relation to this I have just added a new "Case" section. Test that to see if it does what you want.

I have made two other changes to your code

1. I have added two "Application.EnableEvents" lines of code (near the start and end). With your previous code, when a date was entered by the code, this whole Worksheet_Change event code was triggered again only to find the change was not in column Z (therefore pointless) and when the code entered a name, this whole Worksheet_Change event code was triggered yet again only to find the change was not in column Z (therefore pointless). Same if a date gets cleared. The added lines prevent those pointless checks happening.
   
   
2. I presume that all the dates entered should be actual dates, not text dates so I have removed the Format() function from those lines as it returns a text value.

Anyway, give this a try with a copy of your workbook & advise details if further tweaks are required.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Application.Intersect(Target, Columns("Z")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    
    Select Case True
      
      Case InStr(1, Target.Value, "On Hold", vbTextCompare) > 0
        With Target.Offset(0, 2)
          If .Value = "" Then
            .Value = Date
            .NumberFormat = "mm/dd/yyyy"
          End If
        End With
      
        If InStr(1, Target.Value, "-") > 0 Then
          Target.Offset(0, 14).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))
        End If
      
      Case Left(Target.Value, 8) = "Assigned"
        With Target.Offset(0, 10)
          If .Value = "" Then
            .Value = Date
            .NumberFormat = "mm/dd/yyyy"
            If InStr(1, Target.Value, "-") > 0 Then
              Target.Offset(0, 15).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))
            End If
          End If
        End With
      
      Case Left(Target.Value, 15) = "Ready to Assign"
        With Target.Offset(0, 9)
          If .Value = "" Then
            .Value = Date
            .NumberFormat = "mm/dd/yyyy"
          End If
        End With
        
      Case Target.Value = "Reset Hold Date"
        Target.Offset(0, 2).Value = ""
        
      Case Target.Value = "Reset Assigned Date"
        Target.Offset(0, 10).Value = ""
      
      Case Left(Target.Value, 3) = "New"
        With Target.Offset(0, 2)
          If .Value = "" Then
            .Value = Date
            .NumberFormat = "mm/dd/yyyy"
          End If
          Target.Offset(0, 14).Value = Trim(Split(Split(Target.Value, "-")(1), " to ")(0))
        End With
        
    End Select
    
    Application.EnableEvents = True
  End If
End Sub

 

[deftincu]

Better, but still better if the code is indented & not spaced out quite so much, though that may have been affected by your attempts? There is a Test Here forum where you can try out things like that without interfering in a live thread.

I'm not sure if I have fully understood your descriptions, but see if this is getting closer.


In relation to this I have just added a new "Case" section. Test that to see if it does what you want.

I have made two other changes to your code

1. I have added two "Application.EnableEvents" lines of code (near the start and end). With your previous code, when a date was entered by the code, this whole Worksheet_Change event code was triggered again only to find the change was not in column Z (therefore pointless) and when the code entered a name, this whole Worksheet_Change event code was triggered yet again only to find the change was not in column Z (therefore pointless). Same if a date gets cleared. The added lines prevent those pointless checks happening.
   
   
2. I presume that all the dates entered should be actual dates, not text dates so I have removed the Format() function from those lines as it returns a text value.

Anyway, give this a try with a copy of your workbook & advise details if further tweaks are required.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Application.Intersect(Target, Columns("Z")) Is Nothing Then
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
  
    Select Case True
    
      Case InStr(1, Target.Value, "On Hold", vbTextCompare) > 0
        With Target.Offset(0, 2)
          If .Value = "" Then
            .Value = Date
            .NumberFormat = "mm/dd/yyyy"
          End If
        End With
    
        If InStr(1, Target.Value, "-") > 0 Then
          Target.Offset(0, 14).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))
        End If
    
      Case Left(Target.Value, 8) = "Assigned"
        With Target.Offset(0, 10)
          If .Value = "" Then
            .Value = Date
            .NumberFormat = "mm/dd/yyyy"
            If InStr(1, Target.Value, "-") > 0 Then
              Target.Offset(0, 15).Value = Trim(Split(Split(Target.Value, "-")(1), "(")(0))
            End If
          End If
        End With
    
      Case Left(Target.Value, 15) = "Ready to Assign"
        With Target.Offset(0, 9)
          If .Value = "" Then
            .Value = Date
            .NumberFormat = "mm/dd/yyyy"
          End If
        End With
      
      Case Target.Value = "Reset Hold Date"
        Target.Offset(0, 2).Value = ""
      
      Case Target.Value = "Reset Assigned Date"
        Target.Offset(0, 10).Value = ""
    
      Case Left(Target.Value, 3) = "New"
        With Target.Offset(0, 2)
          If .Value = "" Then
            .Value = Date
            .NumberFormat = "mm/dd/yyyy"
          End If
          Target.Offset(0, 14).Value = Trim(Split(Split(Target.Value, "-")(1), " to ")(0))
        End With
      
    End Select
  
    Application.EnableEvents = True
  End If
End Sub

After a few minor tweaks, it’s now functioning as I’d like it too. I really appreciate your assistance!

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.