Help with VBA code

[ssaulen2011]

I'm trying to create a button that will search these cells for the string "AUTO"

F13:F20,F25:F32,F38:F44,F49:F56,F61:F68,J61:J68,N61:N68,J49:J56,N49:N56,J37:J44,N37:N44,J25:J32,N25:N32,J13:J20,N13:N20,R13:R20,R25:R32,V25:V32,V13:V20,Z13:Z20,AD13:AD20,Z25:Z32,AD25:AD32,R37:R44,V37:V44,Z37:Z44,AD37:AD44,AD49:AD56,R49:R56,V49:V56,Z49:Z56

If it doesn't say "AUTO" I want it to clear contents and also clear content of the corresponding cell to the left.

Example If the first cell F13 contains "AUTO" I want it to skip to F14 and so on. but if F14 doesn't contain "AUTO" I want it to clear contents of F14 and E14 and continue on.

Here is a list of the secondary cells I want to clear the contents if the other contains "AUTO"

E13:E20,E25:E32,E38:E44,E49:E56,E61:E68,I61:I68,M61:M68,I49:I56,M49:M56,I37:I44,M37:M44,I25:I32,M25:M32,I13:I20,M13:M20,Q13:Q20,Q25:Q32,U25:U32,U13:U20,Y13:Y20,AC13:AC20,Y25:Y32,AC25:AC32,Q37:Q44,U37:U44,Y37:Y44,AC37:AC44,AC49:AC56,Q49:Q56,U49:U56,Y49:Y56

Anyone help? let me know if this is confusing.

 

[ssaulen2011]

 

[Norie]

Put this code into a standard module,

Sub AutoCheck()
Dim rng As Range
Dim cl As Range

    Set rng = Range("F13:F20,F25:F32,F38:F44,F49:F56,F61:F68,J61:J68,N61:N68,J49:J56,N49:N56,J37:J44,N37:N44,J25:J32,N25:N32,J13:J20,N13:N20,R13:R20,R25:R32,V25:V32,V13:V20,Z13:Z20,AD13:AD20,Z25:Z32,AD25:AD32,R37:R44,V37:V44,Z37:Z44,AD37:AD44,AD49:AD56,R49:R56,V49:V56,Z49:Z56")

    For Each cl In rng.Cells
        If cl.Value <> "AUTO" Then
            cl.Offset(, -1).Value = ""
        End If
    Next cl
    
End Sub

then create a button from the Forms toolbox and assign the macro AutoCheck to it.

 

[ssaulen2011]

this is close to what I already had and works great. Except I want it to clear both the cell that doesn't contain "AUTO" and the cell -1 from it.

 

[Norie]

Try this.

Sub AutoCheck()
Dim rng As Range
Dim cl As Range

    Set rng = Range("F13:F20,F25:F32,F38:F44,F49:F56,F61:F68,J61:J68,N61:N68,J49:J56,N49:N56,J37:J44,N37:N44,J25:J32,N25:N32,J13:J20,N13:N20,R13:R20,R25:R32,V25:V32,V13:V20,Z13:Z20,AD13:AD20,Z25:Z32,AD25:AD32,R37:R44,V37:V44,Z37:Z44,AD37:AD44,AD49:AD56,R49:R56,V49:V56,Z49:Z56")

    For Each cl In rng.Cells
        If cl.Value <> "AUTO" Then
            cl.Offset(, -1).Resize(,2).Value = ""
        End If
    Next cl
    
End Sub

 

[ssaulen2011]

this is close to what I already had and works great. Except I want it to clear both the cell that doesn't contain "AUTO" and the cell -1 from it.

Thanks for the help Norie

I just added this
cl.Value = ""
above
cl.Offset(, -1).Value = ""