vLookup in VBA

[kensandy1]

Could someone tell me why I'm getting a type mismatch error on this code? I've been messing with it for hours and I can't figure out why.

Sub Macro1()


    Dim LookupValue As Double
    Dim VLookupResult As Double
    Dim myTableArray As Range




    LookupValue = TimeValue("5:00:00 AM")


    With Sheets("Sheet1")
        Set myTableArray = .Range(.Cells(2, 1), .Cells(25, 4))
    End With


    MsgBox (LookupValue)
    VLookupResult = Application.VLookup(LookupValue, myTableArray, 1, False)
    
    MsgBox VLookupResult


End Sub

The type mismatch error comes in this line
VLookupResult = Application.VLookup(LookupValue, myTableArray, 1, False)

It's looking at h:mm:ss and I'm trying to return itself when it finds the matching column.

Here's the data I'm using

[TABLE="width: 256"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]One[/TD]
[TD="width: 64"]Two[/TD]
[TD="width: 64"]Three[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0:00:00[/TD]
[TD="class: xl65, align: right"]0:01:00[/TD]
[TD="class: xl65, align: right"]0:30:00[/TD]
[TD="class: xl65, align: right"]1:00:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1:00:00[/TD]
[TD="class: xl65, align: right"]0:02:00[/TD]
[TD="class: xl65, align: right"]0:31:00[/TD]
[TD="class: xl65, align: right"]1:01:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2:00:00[/TD]
[TD="class: xl65, align: right"]0:03:00[/TD]
[TD="class: xl65, align: right"]0:32:00[/TD]
[TD="class: xl65, align: right"]1:02:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3:00:00[/TD]
[TD="class: xl65, align: right"]0:04:00[/TD]
[TD="class: xl65, align: right"]0:33:00[/TD]
[TD="class: xl65, align: right"]1:03:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4:00:00[/TD]
[TD="class: xl65, align: right"]0:05:00[/TD]
[TD="class: xl65, align: right"]0:34:00[/TD]
[TD="class: xl65, align: right"]1:04:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5:00:00[/TD]
[TD="class: xl65, align: right"]0:06:00[/TD]
[TD="class: xl65, align: right"]0:35:00[/TD]
[TD="class: xl65, align: right"]1:05:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]6:00:00[/TD]
[TD="class: xl65, align: right"]0:07:00[/TD]
[TD="class: xl65, align: right"]0:36:00[/TD]
[TD="class: xl65, align: right"]1:06:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]7:00:00[/TD]
[TD="class: xl65, align: right"]0:08:00[/TD]
[TD="class: xl65, align: right"]0:37:00[/TD]
[TD="class: xl65, align: right"]1:07:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]8:00:00[/TD]
[TD="class: xl65, align: right"]0:09:00[/TD]
[TD="class: xl65, align: right"]0:38:00[/TD]
[TD="class: xl65, align: right"]1:08:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]9:00:00[/TD]
[TD="class: xl65, align: right"]0:10:00[/TD]
[TD="class: xl65, align: right"]0:39:00[/TD]
[TD="class: xl65, align: right"]1:09:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]10:00:00[/TD]
[TD="class: xl65, align: right"]0:11:00[/TD]
[TD="class: xl65, align: right"]0:40:00[/TD]
[TD="class: xl65, align: right"]1:10:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]11:00:00[/TD]
[TD="class: xl65, align: right"]0:12:00[/TD]
[TD="class: xl65, align: right"]0:41:00[/TD]
[TD="class: xl65, align: right"]1:11:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]12:00:00[/TD]
[TD="class: xl65, align: right"]0:13:00[/TD]
[TD="class: xl65, align: right"]0:42:00[/TD]
[TD="class: xl65, align: right"]1:12:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]13:00:00[/TD]
[TD="class: xl65, align: right"]0:14:00[/TD]
[TD="class: xl65, align: right"]0:43:00[/TD]
[TD="class: xl65, align: right"]1:13:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]14:00:00[/TD]
[TD="class: xl65, align: right"]0:15:00[/TD]
[TD="class: xl65, align: right"]0:44:00[/TD]
[TD="class: xl65, align: right"]1:14:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]15:00:00[/TD]
[TD="class: xl65, align: right"]0:16:00[/TD]
[TD="class: xl65, align: right"]0:45:00[/TD]
[TD="class: xl65, align: right"]1:15:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]16:00:00[/TD]
[TD="class: xl65, align: right"]0:17:00[/TD]
[TD="class: xl65, align: right"]0:46:00[/TD]
[TD="class: xl65, align: right"]1:16:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]17:00:00[/TD]
[TD="class: xl65, align: right"]0:18:00[/TD]
[TD="class: xl65, align: right"]0:47:00[/TD]
[TD="class: xl65, align: right"]1:17:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]18:00:00[/TD]
[TD="class: xl65, align: right"]0:19:00[/TD]
[TD="class: xl65, align: right"]0:48:00[/TD]
[TD="class: xl65, align: right"]1:18:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]19:00:00[/TD]
[TD="class: xl65, align: right"]0:20:00[/TD]
[TD="class: xl65, align: right"]0:49:00[/TD]
[TD="class: xl65, align: right"]1:19:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]20:00:00[/TD]
[TD="class: xl65, align: right"]0:21:00[/TD]
[TD="class: xl65, align: right"]0:50:00[/TD]
[TD="class: xl65, align: right"]1:20:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]21:00:00[/TD]
[TD="class: xl65, align: right"]0:22:00[/TD]
[TD="class: xl65, align: right"]0:51:00[/TD]
[TD="class: xl65, align: right"]1:21:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]22:00:00[/TD]
[TD="class: xl65, align: right"]0:23:00[/TD]
[TD="class: xl65, align: right"]0:52:00[/TD]
[TD="class: xl65, align: right"]1:22:00[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]23:00:00[/TD]
[TD="class: xl65, align: right"]0:24:00[/TD]
[TD="class: xl65, align: right"]0:53:00[/TD]
[TD="class: xl65, align: right"]1:23:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[kensandy1]

If I change the line to this

VLookupResult = WorksheetFunction.VLookup(LookupValue, myTableArray, 1, False)

I get "Unable to get the VLookup property of the WorksheetFunction class"

 

[steve the fish]

Thats telling you it cant find the lookup value. Check this:

=A7=TIME(5,0,0)

 

[kensandy1]

Why did that work?

It also works if I use the value .208333333333333 instead of a double variable with no formula in the cell.

It'll be easier for me to just change all the cells to that formula. Thanks.