Tell what format each cell is in

[stevewy]

When using Excel for mail merging at work, when we get an address like
"3-5 East Street" and "3-5" is in a cell on its own, separate from
East Street, Excel will change the "3-5" to 3-May (U.K. date format).
This is because it is trying (unsuccessfully) to ascertain the format
of the data in the cell.

We can get round this by changing the cell format to "Text", but then
it will change to something like "39936", Excel's way of storing
dates. Either way, it doesn't do what we want!

Usually, if we notice a cell that has changed to "3 May", it is easy
to figure out what it should have been, and change it manually. This
is not practical on a spreadsheet of 20,000 addresses!

I thought a VBA solution might be possible, if the macro could go
through each cell in the ActiveRange, seeing if it is formatted as
"Date". If it is, the macro could halt with the ActiveCell as that
cell. Then we could locate and change these problem cells quickly.

It seems the property I need is "ActiveCell.NumberFormat", but I am
having trouble getting this to work in a macro. Plus,
ActiveCell.NumberFormat does not return something like "Date", it
returns the date format of that cell. I am also having trouble
getting the macro to stop dead when it discovers a date, and making
the problem cell the ActiveCell, so I can see where it is.

Does anyone have any suggestions as to how I might overcome this
problem? Is VBA the right way to solve it?

Steve

 

[Peter T]

Maybe something like this to reconvert dates from 3-May to "3-5"

Sub test()
Dim s as String
Dim c As Range

For Each c In Range("A1:A10")
If IsDate(c) Then
With c
s = Day(.Value) & "-" & Month(.Value)
.NumberFormat = "@"
.Value = s
End With
End If
Next

End Sub

It might be worth including one or two more If checks before changing
anything, eg

If instr(1, c.Text, "-") then
if instr(1, c.numberformat, "d") then

Instead of changing the numberformat to text, maybe change it to general and
prefix the new string with an apostrophe.

Regards,
Peter T

 

[stevewy]

Well, it needs to be a very general macro because it will be used on
lots of occasions. We process address lists that other applications
have dumped out as CSV. It is when they are imported/opened in Excel
that this unwanted transformation tends to take place.

I think the IsDate VBA function could be useful here. I might try to
work it up into a macro that would check each selected cell and if
IsDate is true, return the address of that cell. Or perhaps find a
way that the cell could be made into ActiveCell so when the macro
stops it is at the right position.

Thank you for your ideas. You have given me a few paths to follow.

Steve

 

[Peter T]

I don't follow your intention of wanting the activecell, that's merely where
the cursor happens to be. For most purposes in VBA you do not need to know
the activecell.

You could make simple change event routine to validate the entry on input,
and if necessary alter it. If you preformat the cells as text before
entering any data the problem shouldn't arise.

Regards,
Peter T

 

[stevewy]

I don't follow your intention of wanting the activecell, that's merely where
the cursor happens to be. For most purposes in VBA you do not need to know
the activecell.

You could make simple change event routine to validate the entry on input,
and if necessary alter it. If you preformat the cells as text before
entering any data the problem shouldn't arise.

The data wouldn't be entered manually. If we extract any data from
our own applications/databases, it would normally extract it to the
clipboard, and we would format the sheet as text before pasting it
in. However, most times we receive the data already in Excel and
occasionally we get this "thinks its a date" problem, which I was
trying to resolve.

As I would not be the only person using this macro, I would prefer it
just to identify possible problem cells rather than changing them
itself. All I really need is a macro that identifies date-format
cells in the active sheet. It may not be the most efficient way, but
it is the way that will work the best for the people who will be using
the macro.

Steve

 

[Peter T]

Have a go with this

- copy your number/date column to another sheet
- add a helper column next to your data with numbers 1,2,3 etc, ie an index
-sort the data column with the helper, potential dates
-Any dates will probably be in the range 39-40,000, typically at the bottom,
or top if you sort descending, at the top.
- copy the obvious date cells immediately adjacent
- *Select* the copied date cells

amend the macro I posted earlier
change

For Each c In Range("A1:A10")

to
For Each c In Selection

run the macro

compare the converted cells with adjacent date cells, hopefully not too many
to look at from the original 20k

If all looks good paste back and re-sort but use the helper index column as
the key
Paste the new number column back to original location

Regards,
Peter T

 

[stevewy]

Great! That sounds like it will work - I will give it a go.

Thanks again.

Steve

 

[Rick Rothstein]

How would you like the date-formatted cells to be identified... a list of
cell addresses, change the cell color, some other way?

 

[stevewy]

A list of any cell addresses formatted as "date", I guess, would
work. Then we could just go through them checking and changing as
necessary. Normally for small macros such as (what I presumed,
perhaps wrongly) this would be, I can bang out a quick procedure with
my limited knowledge of VBA. This task has alluded me...

Steve

 

[Rick Rothstein]

Give the following macro a try. I set it up to search for cells in Column A
(which you can change in the "Worksheet specific settings" section; it can
be a multi-column range if need be) and to look for cells in that column
formatted as "d-mmm" or as "mmm-yy" (again, which you can change in the
"Worksheet specific settings" section). The reason I chose those two formats
is because the date-conversion that Excel performs depends on the size of
the two numbers separated by the dash. For example, a value of 3-5 converted
to 5-Mar (on my US configured system) whereas a value of 3-90 converted to
Mar-90. If you are getting different conversions, than adjust the format
strings inside the Split statement (make sure you don't add any spaces to
"pretty" things up). Okay, that's it... give it a try.

Sub ListConvertedDates()
Dim C As Range, SearchRange As Range
Dim X As Long, RowCount As Long, OutputCol As Long
Dim FirstAddress As String, SearchFormats() As String
Application.ScreenUpdating = False
With Worksheets("Sheet6")
' Worksheet specific settings
Set SearchRange = .Columns("A")
SearchFormats = Split("d-mmm,mmm-yy", ",")
OutputCol = .Columns(.UsedRange.Columns.Count + 1).Column
' End worksheet specific settings
For X = 0 To 1
Application.FindFormat.NumberFormat = SearchFormats(X)
Set C = SearchRange.Find("", SearchFormat:=True, _
SearchOrder:=xlByColumns)
If Not C Is Nothing Then
FirstAddress = C.Address
Do
If C.Value <> "" Then
RowCount = RowCount + 1
.Cells(RowCount, OutputCol).Value = C.Address
End If
Set C = SearchRange.Find("", After:=C, SearchFormat:=True, _
SearchOrder:=xlByColumns)
Loop While Not C Is Nothing And C.Address <> FirstAddress
End If
Next
End With
Application.ScreenUpdating = True
End Sub

 

[stevewy]

Thank you, Rick, for taking the time to write the above macro. I
shall give it a go and post back with how I got on.

Steve