Handling cells with !REF error when copying and pasting range

[tomleitch]

Hi all,

I have some code that is processes data that is held in the clipboard from another sheet.

It's a bit clunky - but it works ok.... basically I go to the second worksheet, select all and copy and then go back to the first sheet and click the button to run the code that imports data.

There is quite a bit of data that it processes - between 4000 to 20000 lines and each line has about 8 different columns that I use to get data from.

Everything works fine - however sometimes there is just the odd entry where in the report field someone has started the entry with an equals or a minus sign so it then appears in my source sheet as !REF error - which gives me a problem when I try and copy and paste it.

I don't really want to loop through all the lines or anything to fix as it would be quite a slow process.

Can anyone give me any advice on the best way to go about sorting this or what options might be?

Thanks
Tom

 

[mole999]

if the plus and minus are unimportant, and the REF doesn't occur until you process you could just remove the offending items

    Columns("A:H").Select
    Selection.Replace What:="+", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False