VBA IF/THEN not working

[EMcK01]

Hi,

Struggling with this and not sure why.
I want to look through the range of cells and offset any cell with essentially a date in it. ie. B3 contains Saturday 26 August 2017, B11 contains Sunday 27 August 2017. There is no regular pattern to this so need to look within all cells in the range. As it covers multiple dates, days, months and years thought searching for *day* would be the simplest solution.

Stepping through the code it appears to see the values in all cells however it doesn't then do the copy offset and clear when it finds *day* within the cell

       Set rngA = Sheets(wsName).Range("B3:B" & numRows)       
           For Each cell In rngA
            If cell.Value = "*day*" Then              
              cell.Copy cell.Offset(0, 5)
              cell.Clear
            End If
        Next cell

Any help on this would be appreciated.

Thanks,
EMcK

 

[RoryA]

The = operator does not use wildcards so you are looking for the literal text "*day*" which clearly isn't there. You can use Instr or Like instead:

If cell.Value Like "*day*" Then

or:

If Instr(1, cell.value, "day", vbtextcompare) <> 0 then

 

[EMcK01]

Thanks for the response and explanation Rory, much appreciated.