Results 1 to 15 of 15
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Inverted date... (2000 sr 1)

    Why when i go with the curson on this parameter the date is inverse mode mm/dd and not dd/mm?????
    The column in refrence is in txt format and i want to maintain that format....

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inverted date... (2000 sr 1)

    Visual Basic is completely USA-oriented. Numbers are shown with a decimal point, dates are shown in mm/dd/yy format. There is NOTHING you can do about it.

    The result in the Excel workbook will still be displayed according to your local settings.

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inverted date... (2000 sr 1)

    HansV this is a big prob...
    But not is possible to solve with a special format
    Remember the problem is located here:
    http://www.wopr.com/cgi-bin/w3t/showthread...ew=&sb=&o=&vc=1
    Is very iomportant for me............

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inverted date... (2000 sr 1)

    But what IS the problem?

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inverted date... (2000 sr 1)

    when the macro compare the date from the column:

    Set rngFind is 11/10/2004
    and
    .Range("L:L").Find(date) is 10/11/2004

    in this case the macro is stopped because the compare param not is true....
    Naturally the macro find 11/10/2004 and not 10/11/2004...

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Inverted date... (2000 sr 1)

    If you search for the current date, VBA displays 11/10/2004, but this is in fact the 10th of November, since VBA uses US date format.

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inverted date... (2000 sr 1)

    Your find command must find according to formula, not value. If by formula it searches by the true content of the cell. In a date it searches for the value like 36543 of stead of the formatting of the date.
    Regards,
    Rudi

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inverted date... (2000 sr 1)

    ... but not is possible ton accrding with formula?

    The macro is here:
    <font face="Script MT Bold">Sub VER_DATA_TAB()

    Dim lngRow As Long
    Dim rngFind As Range
    Dim blnAllOk As Boolean
    Dim lngIndex As Long
    Dim rngOut As Range

    With Worksheets("DB_AGENZIE")
    ' new code
    Set rngFind = .Range("L:L").Find(date)
    If Not rngFind Is Nothing Then
    Exit Sub
    End If

    lngRow = 2
    blnAllOk = True
    ' load userform2 BUT do not show it yet
    Load UserForm2
    UserForm2.ListBox1.Clear
    '
    ' Still put the cell reference in the listbox
    ' as we can use that to update column L
    '

    UserForm2.ListBox1.ColumnWidths = ";0"
    ' test each cell in column K
    Do While .Cells(lngRow, 11) <> ""
    ' try and find match in column L
    Set rngFind = .Range("L:L").Find(.Cells(lngRow, 11))
    If rngFind Is Nothing Then
    ' no match so fill listbox with information
    ' Date not found
    UserForm2.ListBox1.AddItem .Cells(lngRow, 11)
    ' cell address of data not found
    UserForm2.ListBox1.List(lngIndex, 1) = .Cells(lngRow, 11).Address
    lngIndex = lngIndex + 1
    blnAllOk = False

    End If

    lngRow = lngRow + 1
    Loop

    End With

    If blnAllOk Then

    Call ORDINA_TAB
    Call CHIUDI_EXTRA
    Call ADO_TUTTO
    Call CONTA_RECORD
    Call COMPACT_MDB
    Sheets("L0785_TOTALE").Select

    MsgBox "OPERAZIONI TERMINATE. TUTTI I TABULATI ELABORATI!", vbInformation
    Else

    UserForm2.Show

    End If

    End Sub</font face=script>

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inverted date... (2000 sr 1)

    Salvatore

    Just my <img src=/S/2cents.gif border=0 alt=2cents width=15 height=15> worth but could you do this :

    Set rngFind = .Range("L:L").Find(format(date,"DD/MM/YYYY"))

    This is a total shot in the dark as I have not tested it.
    Jerry

  10. #10
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inverted date... (2000 sr 1)

    Peraphs it work!
    I test it and after....

  11. #11
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inverted date... (2000 sr 1)

    no, if in the sheet is present 11/12/2004 it read 12/11/2004....

  12. #12
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inverted date... (2000 sr 1)

    Salvatore

    I have had a play with this, I am sorry if this is not what you want.

    An option you could go for is to change the format of all the cells in a specific column. I wrote this little bit of code to change cell A1 in european format (dd/mm/yy)

    Sub Sal21()
    Dim Dater As Date

    Range("A1").Select

    Dater = Format(ActiveCell.Value, "dd/mm/yy")
    ActiveCell.Value = Dater
    End Sub
    Jerry

  13. #13
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inverted date... (2000 sr 1)

    tks, but to integrate in my script?

  14. #14
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Inverted date... (2000 sr 1)

    In effect if you see in the column K of the sheet is present 11/12/2004 and not 12/11/2004 ... when the variable is filed return 12/11/2004

  15. #15
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Inverted date... (2000 sr 1)

    Hi

    Sub VER_DATA_TAB()

    Dim Dater as Date
    Dim RowCount As Interger
    Dim lngRow As Long
    Dim rngFind As Range
    Dim blnAllOk As Boolean
    Dim lngIndex As Long
    Dim rngOut As Range


    RowCount = Range("L65535").End(xlUp).Row

    Range("L1").Select

    For i = 1 To RowCount
    Dater = Format(ActiveCell.Value, "dd/mm/yy") ' or "mm/dd/yy" for American style date
    ActiveCell.Value = Dater
    ActiveCell.Offset(1, 0).Select

    Next i





    With Worksheets("DB_AGENZIE")
    ' new code
    Set rngFind = .Range("L:L").Find(date)
    If Not rngFind Is Nothing Then
    Exit Sub
    End If

    .........Code continues


    This code hunts down the length of rows in Column L and converts the dates to the format you require, hopefully rngFind will work after that.
    Jerry

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •