+ Reply to Thread
Results 1 to 8 of 8

CDate not working

  1. #1
    Registered User
    Join Date
    04-07-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2010
    Posts
    11

    CDate not working

    I have the following code to populate a ComboBox in User form with a unique list of dates, where the CDate function does not seem to work. I have also used the CLng function but that does not seem to work either. The routine works fine if I have a list of Names using the CStr function.

    Can someone offer me a solution please

    Private Sub UserForm_Initialize()
    Dim Found As Long, i As Long
    Dim cel As Range

    'Create date List
    MyList = "valEndDate" ' This is a list of Dates

    Set DataList = Range(MyList)
    ReDim FArray(DataList.Cells.Count)
    i = -1

    For Each cel In DataList
    On Error Resume Next
    Found = Application.WorksheetFunction.Match(CDate(cel), FArray, 0)
    If Found > 0 Then GoTo Exists
    i = i + 1
    FArray(i) = cel
    Exists:
    Found = 0
    Next
    ReDim Preserve FArray(i)
    Call BubbleSort(FArray)
    ComboBox1.ListRows = i + 1
    ComboBox1.List() = FArray
    End Sub

    Many thanks for assistance/sglxl

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: CDate not working

    I'd use a dictionary for this but your code oughta work like this
    Please Login or Register  to view this content.
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    04-07-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: CDate not working

    I tried that but it didn't work.

    Can you tell me how to use a dictionary so that I can give it atry. This thing is driving me nuts.

    Thanks

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: CDate not working

    Please Login or Register  to view this content.
    this won't work on a mac though

  5. #5
    Registered User
    Join Date
    04-07-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: CDate not working

    I have never used dictionary object before and seems good idea. However I am getting a couple of problems:

    - list of dates are coming up in US format whereas the worksheet is formatted in UK format - dd-mmm-yy
    - I am also getting at least one element from a different column not related to "valEndDates"
    - Finally I do not know why this is happening but the screen also locks and I have no access to the individual cells but can save the workbook, close it and generally use the Ribbon - once wkbk closed and reopened access is available again

    I am using Window 7 with Office 2010

  6. #6
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: CDate not working

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  7. #7
    Registered User
    Join Date
    04-07-2012
    Location
    Athens, Greece
    MS-Off Ver
    Excel 2010
    Posts
    11

    Thumbs up Re: CDate not working

    OK Noted,

    JosephP thanks very much I have found all of the problems they were all my mistakes and have corrected them.

    Everything works fine and thanks very much for all your assistance

  8. #8
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: CDate not working

    Please add the code tags as requested?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1