+ Reply to Thread
Results 1 to 2 of 2

ActiveCell.Value and Time Format Question

  1. #1
    Registered User
    Join Date
    04-11-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    2

    ActiveCell.Value and Time Format Question

    Hello! Anybody have any ideas why this is happening? Thank you!

    I have 2 variables declared as Variants (they are times, and I suppose could use Date as well but have been trouble-shooting):

    dim timeStart as Variant
    dim timeEnd as Variant


    They essentially test if a value in a data set are between an hourly range (for instance: 1:00 AM - 2:00 AM). If the value is between a range, I perform a few calculations and want to report the sum for each data point, for each hour. See the Worksheet image attached.

    Worksheet.jpg

    The problem I am having is when I want to populate this Worksheet I have been running the following code:

    Range("B2").Select 'Selects the time header row
    While ActiveCell.Value <> timeStart 'While the active cell is not equal to the start of the hour value
    ActiveCell.Offset(0, 1).Select
    Wend


    I want to move to the right to column B, C, D, etc until I reach the accurate time, and then fill in the proper row with a returned value.

    Problem: For some reason it seems to work until 2:00 AM. I added a watch, and even though the timeStart value is found to equal 2:00 AM, and the Active Cell is over 2:00 AM, it never leaves the While and just moves to the next time. See attachment:

    Works: works.jpg

    The loop exits and will proceed normally.

    Doesn't Work:doesnt_work.jpg

    Even though the code is the same, timeStart is 2:00:00 AM and the cell is over 2:00:00 AM, it never exits the loop.

    Any ideas? I see the ActiveCell.Value is reading an odd number - but it still works for the times of 0:00 and 1:00. I tried different formatting but no luck. Thanks in advance!
    Last edited by talonboi02; 04-16-2012 at 05:35 PM.

  2. #2
    Registered User
    Join Date
    04-11-2012
    Location
    Philadelphia, PA
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: ActiveCell.Value and Time Format Question

    Found a solution. Apparantly it has to do with Excel's limited precision and the way floating point numbers are stored in binary. I used Application.Match to identify the column I needed.

    Dim lngCol As Long

    lngCol = Application.Match(CDbl(timeStart),Range("2:2"),1)

    'will return the column number applicable to the starttime

    Cells(2,lngCol).Select 'used where I want to call the column

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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