Excel VBA Code to Update Existing Value in Column based on value in another column

[MHamid]

Hello,

I need some help figuring out how to update the below code to update existing values in column AR based on if specific text exists in column AP.
I'm pretty sure I will need to remove the "ClearContents" line, but not sure what I will need to do and was confused with what I found online.
I can provide sample data if needed, but a preliminary push to in the right direction would help.

Sub RemoveOT_Tagging()

Dim sh As Worksheet
Dim lr As Long
Dim i&, rngZ, rngK, rngE, Z As String, K As String, E As String, RemoveTagging()
Set sh = Sheets("Audit_Plan")
lr = sh.Range("J" & Rows.Count).End(xlUp).Row
rngZ = sh.Range("AR7:AR" & lr).Value: rngK = sh.Range("AP7:AP" & lr).Value: rngE = sh.Range("AN7:AN" & lr).Value
ReDim CleanBusImp(1 To UBound(rngZ), 1 To 1)
    For i = 1 To UBound(rngZ)
        Z = rngZ(i, 1): K = rngK(i, 1): E = rngE(i, 1)
        
        Select Case True
            Case (E Like "O&T Area")
            'Code to remove ICG O&T, PBWM O&T and LF - PBWM O&T of there is no indication drilled down
                If (K = "ICG Operations") And (Z Like "*ICG O&T*") Then
                    RemoveTagging(i, 1) = Replace(Replace(Replace(Z, "/ICG O&T", ""), "ICG O&T/", ""), "ICG O&T", "")
                ElseIf (K = "ICG Technology") And (Z Like "*ICG O&T*") Then
                    RemoveTagging(i, 1) = Replace(Replace(Replace(Z, "/ICG O&T", ""), "ICG O&T/", ""), "ICG O&T", "")
                ElseIf (K = "LF - PBWM Operations") And (Z Like "*LF - PBWM O&T*") Then
                    RemoveTagging(i, 1) = Replace(Replace(Replace(Z, "/LF - PBWM O&T", ""), "LF - PBWM O&T/", ""), "LF - PBWM O&T", "")
                ElseIf (K = "LF - PBWM Technology") And (Z Like "*PBWM O&T*") Then
                    RemoveTagging(i, 1) = Replace(Replace(Replace(Z, "/LF - PBWM O&T", ""), "LF - PBWM O&T/", ""), "LF - PBWM O&T", "")
                ElseIf (K = "PBWM Operations") And (Z Like "*PBWM O&T*") Then
                    RemoveTagging(i, 1) = Replace(Replace(Replace(Z, "/PBWM O&T", ""), "PBWM O&T/", ""), "PBWM O&T", "")
                ElseIf (K = "PBWM Technology") And (Z Like "*PBWM O&T*") Then
                    RemoveTagging(i, 1) = Replace(Replace(Replace(Z, "/PBWM O&T", ""), "PBWM O&T/", ""), "PBWM O&T", "")
                End If
        End Select
    Next

With sh.Range("AR7").Resize(UBound(rngZ), 1)
    .ClearContents
    .Value = RemoveTagging
End With


End Sub

Thank you,

 

[DanteAmor]

Hi @MHamid . Thanks for posting on the forum.

I have a couple of observations:

1. The first condition that must be fulfilled is that cell AN has this text: "O&T Area", but I suppose it must be between asterisks:​

Case (E Like "*O&T Area*")

2. Continuing with the pattern of the conditions, here you are missing the letters "LF -"​

 ElseIf (K = "LF - PBWM Operations") And (Z Like "*LF - PBWM O&T*") Then
   RemoveTagging(i, 1) = Replace(Replace(Replace(Z, "/LF - PBWM O&T", ""), "LF - PBWM O&T/", ""), "LF - PBWM O&T", "")
ElseIf (K = "LF - PBWM Technology") And (Z Like "*LF - PBWM O&T*") Then

-------------------------​

I tried to simplify the code. Please try the following:

Sub RemoveOT_Tagging_v1()
  Dim sh As Worksheet
  Dim i As Long, j As Long
  Dim a As Variant, b As Variant, ar1 As Variant, ar2 As Variant
 
  Set sh = Sheets("Audit_Plan")
  a = sh.Range("AN7:AR" & sh.Range("AR" & Rows.Count).End(xlUp).Row).Value
  ReDim b(1 To UBound(a), 1 To 1)
 
  ar1 = Array("ICG Operations", "ICG Technology", "LF - PBWM Operations", "LF - PBWM Technology", "PBWM Operations", "PBWM Technology")
  ar2 = Array("ICG O&T", "ICG O&T", "LF - PBWM O&T", "LF - PBWM O&T", "PBWM O&T", "PBWM O&T")

  For i = 1 To UBound(a)
    If a(i, 1) Like "*O&T Area*" Then
      For j = 0 To UBound(ar1)
        If a(i, 3) = ar1(j) And a(i, 5) Like "*" & ar2(j) & "*" Then
          b(i, 1) = Replace(Replace(Replace(a(i, 5), "/" & ar2(j), ""), ar2(j) & "/", ""), ar2(j), "")
        End If
      Next
    End If
  Next
 
  sh.Range("AR7").Resize(UBound(b)).Value = b
End Sub

I tried with the following:

Dante Amor
	AN		AP		AR
6	E		K		Z
7	O&T Area		ICG Operations		ini /ICG O&T fin ICG O&T
8	O&T Area		ICG Technology		ini ICG O&T/ fin
9	O&T Area		LF - PBWM Operations		ini /LF - PBWM O&T fin
10	O&T Area		LF - PBWM Technology		ini /LF - PBWM O&T fin
11	O&T Area		PBWM Operations		ini /PBWM O&T fin
12	O&T Area		PBWM Technology		ini PBWM O&T/ fin
Audit_Plan

The result:

Dante Amor
	AN		AP		AR
6	E		K		Z
7	O&T Area		ICG Operations		ini fin
8	O&T Area		ICG Technology		ini fin
9	O&T Area		LF - PBWM Operations		ini fin
10	O&T Area		LF - PBWM Technology		ini fin
11	O&T Area		PBWM Operations		ini fin
12	O&T Area		PBWM Technology		ini fin
Audit_Plan

Note:
If the result is not what you are looking for, then give an example of your data and the expected result. Use XL2BB tool.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​