Find and replace

[Jockster]

Hi,
I am trying to write code which will loop down column "B" and, where the text "example text 12345678" is found, replace the numbers (i.e the last eight characters of the string" with "33344455".
I have tried:

If InStr(1, Range("B" & i).Value, "3009020201") Then
Range("B" & i).Value = Left(Range("B" & i), Len(Range("B" & i) - 8)) & "12345678"

but I get a 'type mismatch' error.
Any ideas?

 

[VoG]

Try

If InStr(1, Range("B" & i).Value, "3009020201") > 0 Then
    Range("B" & i).Value = Left(Range("B" & i).Value, Len(Range("B" & i).Value) - 8) & "12345678"

 

[JayHam]

You were trying to subtract 8 from a string (len bracket is in wrong place). See below;

If InStr(1, Range("B" & i).Value, "3009020201") Then
Range("B" & i).Value = Left(Range("B" & i), Len(Range("B" & i)) - 8) & "12345678"

 

[Jockster]

Thanks VoG - sorted!