vba - clear contents of cell if adjacent cell contains part of text

[hrayani]

Hello Friends,

I need a vba code that would clear contents of cell in a range B1:B4 if adjacent cell Column A contains text Total

Pls see below table

Book2
	A	B
1	1234	01-Jun
2	2345	01-Jun
3	3456	01-Jun
4	Total Set(s)	01-Jun
Sheet1

any help would be appreciated

regards,

humayun

 

[Fluff]

How about

Sub hrayani()
   With ActiveSheet
      .Range("A1:B1").AutoFilter 1, "*Total*"
      .AutoFilter.Range.Offset(1).Columns(2).Value = ""
      .AutoFilterMode = False
   End With
End Sub

 

[hrayani]

How about

Sub hrayani()
   With ActiveSheet
      .Range("A1:B1").AutoFilter 1, "*Total*"
      .AutoFilter.Range.Offset(1).Columns(2).Value = ""
      .AutoFilterMode = False
   End With
End Sub

Hi Fluff,

Sorry for coming back late on this.... Can we just restrict the range like A1:B4
There is some other stuff just under the range

 

[Rick Rothstein]

Does this work the way you want...

Sub ClearTotalValues()
  With Range("A1:A4")
    .Offset(, 1).Value = Evaluate(Replace("IF(isnumber(search(""total"",@)),"""",Offset(@,0,1))", "@", .Address))
  End With
End Sub

 

[hrayani]

Does this work the way you want...

Sub ClearTotalValues()
  With Range("A1:A4")
    .Offset(, 1).Value = Evaluate(Replace("IF(isnumber(search(""total"",@)),"""",Offset(@,0,1))", "@", .Address))
  End With
End Sub

Hi Rick, Thanks for the reply

Yes it does on the first click (clear the contents)
but after the second click it inserts 00-Jan in there coz the cells are already empty after the first click

Could you also please look at the below code
only this part If Cl.Value = "Total" Then needs to be amended to make it work. It does if the cell only has Total in there but it does not work if there is Total Sets or something like that...

I tried with If Cl.Value = "*Total*" Then (putting the Asterisk sign but to no avail

Sub DelStates()

    Dim Cl As Range
    
    For Each Cl In Range("A1:B28")
        If Cl.Value = "Total" Then
            Cl.Offset(0, 1).ClearContents
        End If
    Next Cl
    
End Sub

 

[Rick Rothstein]

How about this macro then...

Sub ClearTotalValues()
  With Range("A1:A4")
    .Offset(, 1).Value = Evaluate(Replace("IF(@="""","""",IF(ISNUMBER(SEARCH(""total"",@)),"""",OFFSET(@,0,1)))", "@", .Address))
  End With
End Sub

 

[hrayani]

How about this macro then...

Sub ClearTotalValues()
  With Range("A1:A4")
    .Offset(, 1).Value = Evaluate(Replace("IF(@="""","""",IF(ISNUMBER(SEARCH(""total"",@)),"""",OFFSET(@,0,1)))", "@", .Address))
  End With
End Sub

Done... Thanks very much Rick

But also tell me how to amend this part [If Cl.Value = "Total" Then] of the below code to work
I am still at learning stage

Sub DelStates()

    Dim Cl As Range
    
    For Each Cl In Range("A1:A28")
        If Cl.Value = "*Total*" Then
            Cl.Offset(0, 1).ClearContents
        End If
    Next Cl
    
End Sub

 

[Rick Rothstein]

Assuming you know the word "Total" will always appear in proper case (upper case T, rest of the letters in lower case), you can use the Like operator to do your wildcard test...

If Cl.Value Like "*Total*" Then

If you cannot be sure of the letter casing, then use this instead...

If LCase(Cl.Value) Like "*total*" Then

 

[hrayani]

Assuming you know the word "Total" will always appear in proper case (upper case T, rest of the letters in lower case), you can use the Like operator to do your wildcard test...

If Cl.Value Like "*Total*" Then

If you cannot be sure of the letter casing, then use this instead...

If LCase(Cl.Value) Like "*total*" Then

Works like a charm

Many Thanks

Regards,
Humayun

 

[hrayani]

Assuming you know the word "Total" will always appear in proper case (upper case T, rest of the letters in lower case), you can use the Like operator to do your wildcard test...

If Cl.Value Like "*Total*" Then

If you cannot be sure of the letter casing, then use this instead...

If LCase(Cl.Value) Like "*total*" Then

Just one last thing

what if does not contain
can we using anything like Not Like "*Total*"