Hello,
I'm trying to figure out a solution for this:
I have a table similiar to this one and need to have a "whitelist" that can filter every line from the J column and replace it with "DONE" if it is not on the whitelist.
I currently have a simple vba written for a blacklist but because I have so many entries it is getting to long...
This is what I have:
and so on...
I'm trying to figure out a solution for this:
I have a table similiar to this one and need to have a "whitelist" that can filter every line from the J column and replace it with "DONE" if it is not on the whitelist.
I currently have a simple vba written for a blacklist but because I have so many entries it is getting to long...
This is what I have:
VBA Code:
Find_Text = "brown"
Replace_Text = "done"
Set Rng = Range("J2:J190")
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Count = 0
For k = 1 To Len(Rng.Cells(i, j))
If Mid(Rng.Cells(i, j), k, Len(Find_Text)) = Find_Text Then
Count = Count + 1
End If
Next k
If Count > 0 Then
For k = 1 To Len(Rng.Cells(i, j)) + Count * Abs(Len(Replace_Text) - Len(Find_Text)) - Len(Find_Text) + 1
If Mid(Rng.Cells(i, j), k, Len(Find_Text)) = Find_Text Then
Rng.Cells(i, j) = Left(Rng.Cells(i, j), k - 1) + Replace_Text + Right(Rng.Cells(i, j), Len(Rng.Cells(i, j)) - k + 1 - Len(Find_Text))
k = k + Len(Replace_Text)
End If
Next k
End If
Next j
Next i
Find_Text = "grey"
Replace_Text = "tareas tratadas"
Set Rng = Range("J2:J190")
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Count = 0
For k = 1 To Len(Rng.Cells(i, j))
If Mid(Rng.Cells(i, j), k, Len(Find_Text)) = Find_Text Then
Count = Count + 1
End If
Next k
If Count > 0 Then
For k = 1 To Len(Rng.Cells(i, j)) + Count * Abs(Len(Replace_Text) - Len(Find_Text)) - Len(Find_Text) + 1
If Mid(Rng.Cells(i, j), k, Len(Find_Text)) = Find_Text Then
Rng.Cells(i, j) = Left(Rng.Cells(i, j), k - 1) + Replace_Text + Right(Rng.Cells(i, j), Len(Rng.Cells(i, j)) - k + 1 - Len(Find_Text))
k = k + Len(Replace_Text)
End If
Next k
End If
Next j
Next i
and so on...