Bizarre behaviour in find and replace macro (help!)

[Oliver Dewar]

Hello!

I have a macro that goes through the values in a certain range and replaces 'tokens' I've set up (e.g. +State+) with the corresponding value from the current records column.

For all the instances where the new text is one or two words everything works beautifully. But for the tokens that should be replaced by a string of html anchorlinks I get "Error 13 type mismatch"... and that's not all... one token is apparantly totally missed in the sequence!

Here's a chunk of my code:

Range("AT5:AT15").Replace What:="+Locality+", Replacement:=Cells(ActiveCell.Row, "C").Value, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

Range("AT5:AT15").Replace What:="+State+", Replacement:=Cells(ActiveCell.Row, "D").Value, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    If Cells(ActiveCell.Row, "G").Value = 7 Then
        Range("AT5:AT15").Replace What:="+Suburb-links+", Replacement:=Cells(ActiveCell.Row, "K").Value, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

        Else

        Range("AT5:AT15").Replace What:="+Loc-Backlink+", Replacement:=Cells(ActiveCell.Row, "Q").Value, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    End If
    
Range("AT5:AT15").Replace What:="+Serv-links+", Replacement:=Cells(ActiveCell.Row, "L").Value, LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

So at the moment the if is testing false and the +Loc-Backlink+ token is the one that causes the error 13. The +Serv-Links+ token is the one that is missed altogether. I've double checked the spelling and it's correct.

Any ideas?

 

[Ruddles]

You get Error 13 when you try to do maths on non-numeric things, and +Loc-Backlink+ certainly looks as though it's inviting VBA to do maths with it. (I don't know why it would actually do that though.)

Have you tried using symbols which don't have a mathematical connotation, if only to eliminate that possibility?