search formula returns #VALUE!

[viggie79]

Im trying to sort out which cells have 'move' in a column so I can separate them out easily. This is probably a huge noob move but this was the way I went with. Im having a problem with my if statement return and just cant figure it out.

I have a worksheet with the following code

With Range("P1").Resize(lRow, 1)    .Formula = "=IF(SEARCH(""move"",R[0]C[-12],1),""move"",""x"")"  ': .Value = .Value
  End With

The search function works right because I get "move" in every appropriate corresponding cell, but if there is no 'move' in the searched cell it returns #VALUE! instead of 'x' and I can't figure out why.

Ive tried these as well

With Range("P1").Resize(lRow, 1)    .Formula = "=IF(SEARCH(""move"",R[0]C[-12],1),""move"")"  ': .Value = .Value
  End With

just trying to get a blank cell

With Range("P1").Resize(lRow, 1)    .Formula = "=IF(SEARCH(""move"",R[0]C[-12],1),""move"","""")"  ': .Value = .Value
  End With

With Range("P1").Resize(lRow, 1)    .Formula = "=IF(SEARCH(""move"",R[0]C[-12],1),""move"","x")"  ': .Value = .Value
  End With

but I cant seem to get it.

I can live with it returning #Value!, but Id really like to understand what Im doing wrong.
Googling has confused me more because I keep finding #VALUE! being associated with mixing numbers and letters and trying to do math with those, which I don't think is the case here.

 

[sericom]

You could set the result of a no match to false like this

.Formula = "=IF(IFERROR(SEARCH(""move"",R[0]C[-12],1),FALSE),""move"",""x"")"

 

[viggie79]

Thanks so much!