Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tell what format each cell is in
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tell what format each cell is in
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 wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tell what format each cell is in
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tell what format each cell is in
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 wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tell what format each cell is in
On 16 July, 13:54, "Peter T" <peter_t@discussions wrote:
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tell what format each cell is in
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 wrote in message ... On 16 July, 13:54, "Peter T" <peter_t@discussions wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tell what format each cell is in
Great! That sounds like it will work - I will give it a go.
Thanks again. Steve |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tell what format each cell is in
How would you like the date-formatted cells to be identified... a list of
cell addresses, change the cell color, some other way? -- Rick (MVP - Excel) wrote in message ... On 16 July, 13:54, "Peter T" <peter_t@discussions wrote: 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tell what format each cell is in
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tell what format each cell is in
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 -- Rick (MVP - Excel) wrote in message ... 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tell what format each cell is in
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lock Cell Format - Allow copy and paste of data without format change | Excel Worksheet Functions | |||
cell format - remove cell format pattern without effecting colors | Excel Discussion (Misc queries) | |||
Can cell format come from and change with reference cell format | Excel Discussion (Misc queries) | |||
Excel Auto-Format cell to email format | Excel Discussion (Misc queries) | |||
How do I copy data in single cell format to a merged cell format | Excel Discussion (Misc queries) |