MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Imported cell hell


Posted by Tom Urtis on June 20, 2001 1:25 PM

Hi everybody,

In Excel 2000 I am creating an application that automates a report from a dynamic range of around 20 columns and 2000 rows. The original data is extracted from an external software program and downloaded into Excel, then Save As’d into a separate template file where the data report macro is.

Many columns are date-related (date funded, date shipped, date received, etc). When no date exists in a cell, the extracted download displays two dashes instead of a blank cell. Really, the content of the cell is 2 spaces, a dash, 3 spaces, and a dash. I have tried filtering every which way I can think of, and even though those lousy cells look the same and have the same syntax, a filter (such as =*-*) will work on some but not all such cells. Go figure.

I’ve experimented with alternative formatting, filter criteria, find & replace, and curse words directed at the computer. Nothing has been accomplished except that my African Grey parrot’s swearing vocabulary has increased dramatically this week.

Any suggestions on how to turn those kinds of stubborn imported cells into regular workable cells ?

Thanks.

Tom Urtis


Posted by mseyf on June 20, 2001 2:44 PM

you could select a range of dates and try something like:

Sub ClearBadDate()
Dim x
Dim z As Range

On Error GoTo CheckCell

For Each z In Selection
z.Activate
x = DateValue(ActiveCell)
Next
Exit Sub

CheckCell:
If Err.Number = 13 Then ActiveCell.Clear
Resume Next

End Sub

Hope this gets you pointed in the right direction

-Mark