Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong date-formating while using data fields?
Hello,
I have the following problem: If a call the following makro: Public Sub Test() Dim arrTable As Variant Dim ws As Worksheet Set ws = ActiveSheet arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)) ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)) = arrTable End Sub and in Cell(1,1) is a date in European Format (24.12.2004) the data field arrTable writes the date in American format (12/24/2004) back. This does not happen, if I don't use date field, but normal variables. On my computer I habe Excel 2000 and Excel XP. It only happens with Excel XP, but works right for Excel 2000. The Country code on my computer is "German". Can anyone help me? Thanks, Hermann |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong date-formating while using data fields?
Hi Sascha,
and in Cell(1,1) is a date in European Format (24.12.2004) the data field arrTable writes the date in American format (12/24/2004) back. arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)) ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)) = arrTable In both of these, you're using the (default) .Value of the range, which includes data types based on the cell formatting. Try using .Value2, which doesn't use the formatting to identify data types: arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value2 ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value2 = arrTable Regards Stephen Bullen Microsoft MVP - Excel Professional Excel Development The most advanced Excel VBA book available www.oaltd.co.uk/ProExcelDev |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong date-formating while using data fields?
Thank you Stephen,
your tip was good. I use it now in the following way: arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value2 = arrTable because I need, the information, if a cell is a date or a number. But it only works, if I don't want change the format of a cell (e.g. from date to number). I really do not unterstand, why my code with only ".Value" does not work under my Excel XP. It works with Excel 2000. Thanks, Sascha Stephen Bullen wrote: Hi Sascha, and in Cell(1,1) is a date in European Format (24.12.2004) the data field arrTable writes the date in American format (12/24/2004) back. arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)) ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)) = arrTable In both of these, you're using the (default) .Value of the range, which includes data types based on the cell formatting. Try using .Value2, which doesn't use the formatting to identify data types: arrTable = ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value2 ws.Range(ws.Cells(1, 1), ws.Cells(2, 2)).Value2 = arrTable Regards Stephen Bullen Microsoft MVP - Excel Professional Excel Development The most advanced Excel VBA book available www.oaltd.co.uk/ProExcelDev |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Wrong date-formating while using data fields?
Hi Sascha,
I really do not unterstand, why my code with only ".Value" does not work under my Excel XP. It works with Excel 2000. No idea. This was a nasty bug in earlier versions of Excel (i.e. international handling of dates, times, formulae etc when entered using arrays), that I thought was fixed in Excel 2000. Maybe it's made a comeback. Regards Stephen Bullen Microsoft MVP - Excel Professional Excel Development The most advanced Excel VBA book available www.oaltd.co.uk/ProExcelDev |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting data - re-formating data to "date" format | Excel Discussion (Misc queries) | |||
Formating pre-sets are all wrong | Excel Discussion (Misc queries) | |||
Excel SHOULD NOT AUTO-CHANGE formated text fields to DATE FIELDS! | Excel Worksheet Functions | |||
Autofilter on date fields should refelct the underlying data (rat. | Excel Discussion (Misc queries) | |||
User Forms - Formating fields | Excel Programming |