CODEWITHSUNDEEP
excelvbafor-looplogic
Alfabit
Alfabit

Reputation: 59

Excel VBA Cell Values showing up as intergers

I'm working on a program with Excel's VBA functionality, but ran into a very strange logic error. 

For SheetIndex = 1 To 6
    With ActiveSheet
    For ColIndex = 2 To 6
        For DateIndex = 0 To MinLimit

        datethingy = .Cells(1, ColIndex).Value

        If (.Cells(1, ColIndex).Value = Date_Array(DateIndex, 1)) Then

            For RowIndex = 2 To 11

'           Compare every time slot value here to every time slot value in the array
            datethingy = Trim(CStr(.Cells(RowIndex, 1).Value)) 'ERROR OCCURS HERE

            If (Trim(CStr(.Cells(RowIndex, 1).Value)) = Date_Array(DateIndex, 2)) Then
                .Cells(RowIndex, ColIndex).Value = "Checked"
            End If

            Next
        End If
        Next
    Next
    End With

    SheetIndex = SheetIndex + 1
    Application.Worksheets(SheetIndex).Activate
Next

So in the code above, I go through a series of cell values and make comparisons to values I already have in my array. However, when I draw the values from the cells, rather than "8:30", it comes up as "0.354166666...7". I have no idea why it's coming up like this, I'm even making sure it's being compared as a string and not as an int or anything else.

Here is where I set the values for the sheet's cells. 

.Cells(2, 1).Value = "8:30"
.Cells(3, 1).Value = "9:00"
.Cells(4, 1).Value = "10:15"
.Cells(5, 1).Value = "11:30"
.Cells(6, 1).Value = "12:30"
.Cells(7, 1).Value = "13:30"
.Cells(8, 1).Value = "14:45"
.Cells(9, 1).Value = "16:00"
.Cells(10, 1).Value = "17:15"
.Cells(11, 1).Value = "18:15"

With Range("A2", "A11")
    .HorizontalAlignment = xlCenter
    .Font.Bold = True
    .ColumnWidth = 15
End With

ActiveSheet.Cells.Rows.AutoFit

Does anyone have any idea why this could be happening?

Upvotes: 1

Views: 259

Answers (1)

Sobigen
Sobigen

Reputation: 2169

Excel stores times and dates as numbers. Each whole number is a day and hours, minutes and seconds are broken down as fractions of that day. 8:30 is just a time so there is no whole number and 8.5/24 = 0.3541667.

You can test this with this code and this may provide you a way to format your inputs. Type 8:30 into cell A1

Sub test()
    Debug.Print sheet1.Range("A1").Value = "8:30" 'returns false
    Debug.Print Format(sheet1.Range("A1").Value, "h:mm") = "8:30" 'returns true
    Debug.Print Sheet1.Range("A1").Text = "8:30" 'return true
End Sub

Upvotes: 1

Related Questions