Clear Contents of cell in row that meets wild card criteria in different column in same row

[eyoung4909]

I have been working on a vba macro for a spreadsheet that i am setting up for accounting. What I need to do is to have the vba check the data in column F and if the criteria matches then I need it to clear the contents in column E of the same row as column F. I have it working to get an exact match for the cell but what I need is a way to do a wild card search with a few key words. The code that works for the exact match is:

Sub test1()


For Each Cell In Range("F3:F9999")
If UCase(Cell) = "IL 9.0% (IL 9% SALES TAX)" Then Cell.Offset(0, -1).ClearContents
Next
    
End Sub

putting in "IL 9.0% (IL 9% SALES TAX)" works every time but we have quite a few different ways that Quick Books lists out the sales tax for each state and each percentage of sales tax in the exported spreadsheet that I am working from. I was hoping that there was a way to "wild card" the word "Sales Tax" (since the words sales tax is constant in every variation) into the code to replace having to have each and every available type "Sales tax" entry in the code.

 

[jim may]

I'm not clear with what you are providing. Do you want to Clear the Contents of All E Column row Cells that have "*Sales Tax*" appearing anywhere in the same row cell of Column F?

 

[eyoung4909]

Yes that is correct. I have tried various codes but I cannot seem to get it right. The closest that I have gotten for using a wildcard "*Sales Tax*" put excel in a processing loop of checking column F over and over again."*Sales Tax*" would appear for one of the 25 ways that it is listed in QuickBooks. I used ucase(Cell) because it can appear all uppercase, all lowercase or a mix.

 

[jim may]

Note sample below LIMITS range to Rows 2 - 20; change to your actual Range of cells.
HTH,
Jim

Sub test1()
Dim C As Range
For Each C In Range("F2:F20")
If C.Value Like "*SALES TAX*" Then
C.Offset(0, -1).ClearContents
End If
Next    
End Sub

 

[eyoung4909]

I added the code into the spreadsheet that I am working on and it works perfectly. Thank you so very much for helping me on this sticking point I really appreciate it.

 

[Rick Rothstein]

Here is another macro that you can consider which does not make use of a loop to do its work...

[table="width: 500"]
[tr]
	[td]Sub SalesTaxClearer()
  With Range("F1", Cells(Rows.Count, "F").End(xlUp))
    .Offset(, -1) = Evaluate("IF(ISNUMBER(SEARCH(""Sales Tax""," & .Address & ")),"""",IF(" & .Offset(, -1).Address & "="""",""""," & .Offset(, -1).Address & "))")
  End With
End Sub[/td]
[/tr]
[/table]

 

[jim may]

Glad I could help. The "LIKE" function is ideal for such problems.