String converts to date, but shouldn't 1

[stillwillyboy]

I have the work date, employee number, name, and some other info with headers all starting in A1.
Work Date Numb Name Hrs
04-03-06 1234 Bob 6.0
04-04-06 1234 Bob 6.0
04-05-06 1234 Bob 6.0
04-06-06 1234 Bob 6.0
04-07-06 1234 Bob 6.0
04-03-06 4567 Joe 7.0
04-04-06 4567 Joe 7.0
04-05-06 4567 Joe 7.0
04-06-06 4567 Joe 7.0
04-07-06 4567 Joe 7.0
04-03-06 9999 Sue 5.0
04-04-06 9999 Sue 5.0

After using Data/subtotal, and moving the number and name a bit, I end up with:

COL A B C
Work Date Name Hrs
04-03-06 6.0
04-04-06 6.0
04-05-06 6.0
04-06-06 6.0
04-07-06 6.0
1234 Total Bob 30.0
04-03-06 7.0
04-04-06 7.0
04-05-06 7.0
04-06-06 7.0
04-07-06 7.0
4567 Total Joe 35.0
04-03-06 5.0
04-04-06 5.0
9999 Total Sue 10.0

I delete the detail and end up with:
ColA ColB ColC
1686 Total Bob 30.0
4567 Total Joe 35.0
8888 Total Sue 10.0

So far, so good. But when I remove the word “Total” the info in A1, changes to a date field (8/12/1904). The other info in ColA keeps its string status with 4567 and 8888. Here is my code for removing the word “Total”. I am using CStr to convert, but it's not working.

Sub RemoveWordTotalFromPRVY6EPI()
Application.StatusBar = "Removing Word Total From PRVY6EPI"
Application.ScreenUpdating = False
Dim FixedHours As Single
Sheets("PRVY6EPI").Select
Range("A1").Select
Do
If Right(ActiveCell, 5) = "Total" Then
ActiveCell = CStr(Left(ActiveCell, 4))
End If
ActiveCell.Offset(1, 0).Select
Loop Until ActiveCell = ""
Range("a1").Select
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub

How can I keep A1 from coverting to a date?

Thanks, Bill

 

[AnotherHiggins]

The CELLS are formatted as date, because that is what column A originally stored.

Add this before getting rid of the ' Total' from the strings:

Columns("A:A").NumberFormat = "@"

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

FYI:

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}