Need assistance with VBA

[deftincu]

Hi all,

Hoping someone can help with code, as I am still fairly new to VBA (with all the assistance I’ve received in previous posts, I have learned quite a bit - thank you!).

I have a drop down list in range AA:AA which contains several statuses. I have created the below code which offsets the adjacent cell in column G, concatenating “-Cancel” with the already existing value in the cell, when the status “Cancel Pending” or “Cancel” is chosen from the drop down. Under normal circumstances, pending is chosen first, followed by cancel; however, there are certain situations where only cancel will be chosen, therefore, it is imperative that either one of those two statuses will initiate the “-Cancel” in column G.

The issue I am trying to solve for is that “-Cancel” is entered each time the status changes, resulting in “-Cancel-Cancel” being added to the cell. I would like for the code to check if “-Cancel” already exists in the cell, and, if so, no value is entered. As you can see, I attempted to use <> and wild card, but it does not work (I honestly didn’t expect it to either). I am unsure of how to combine a partial match search of the value in the offset cell with what I currently have. Let me know if any additional info is needed. Thank you in advance for your assistance!

Case Left(Target.Value, 6) = "Cancel"
        With Target.Offset(0, -20)
               If .Value <> "*Cancel*" Then
                   .Value = .Value & "-Cancel"
               End If
         End With

 

[Alex Blakenburg]

If it is always going to be either just Cancel or that Cancel will appended as the last addition couldn't you just check for Cancel in the last position ?

    Case Left(Target.Value, 6) = "Cancel"
            With Target.Offset(0, -20)
                   If Right(.Value, 6) <> "Cancel" Then
                       .Value = .Value & "-Cancel"
                   End If
             End With

 

[deftincu]

If it is always going to be either just Cancel or that Cancel will appended as the last addition couldn't you just check for Cancel in the last position ?

    Case Left(Target.Value, 6) = "Cancel"
            With Target.Offset(0, -20)
                   If Right(.Value, 6) <> "Cancel" Then
                       .Value = .Value & "-Cancel"
                   End If
             End With

I didn’t even think of that! It worked perfectly, thanks so much!

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.