WorksheetFunction.HLookUP 1004 Error

[jdgalaway]

Can someone please take a look at my code. I'm having trouble figuring out what is causing this error.

I'm using a simple hlookup worksheetfunction, and I'm getting a runtime error 1004. The code that I included is a simplified version of a much larger macro. I've indicated in which row the error occurs, but please let me know if you need any additional information.

I appreciate it.

Sub Test()
    Dim i As Integer
    For i = 1 To 23
            
            Dim numRows As Integer
            Dim netRows As Integer
            Dim psmLook As Variant
            Dim psmVal As Variant
            Dim psmRange As Range
            Dim psmNum As Range
            
            numRows = 23
            netRows = 504
        
            psmVal = Worksheets("DetailedSummary").Cells(i + 3, 34)

            'This is supposed to be all one line.  Also, the below line is probably what is causing the error below.
            Set psmRange = Workbooks("Book1").Worksheets("Solve").Range(Workbooks("Book1").Worksheets("Solve").Cells(numRows + 7, 6),     
            Workbooks("Book1").Worksheets("Solve").Cells(numRows + numRows + 7, netRows + 5))
            
            Set psmNum = Worksheets("Solve").Cells(numRows + 7 + i, 4)
            
                       
            psmLook = Application.WorksheetFunction.HLookup(psmVal, psmRange, psmNum, False) <----Error Occurs Here!!!!
            
        
            If Err.Number <> 0 Then
                Worksheets("DetailedSummary").Cells(i + 3, 33).Value = 0
            Else
                Worksheets("DetailedSummary").Cells(i + 3, 33).Value = psmLook
            End If
    Next

End Sub

[Aussiebear]

The setting of the PSM Range seems odd? Just what are you hoping to find here? Given that you say the error occurs in the PSMNum line of code, we need then to ascertain what the values are for the Look up value, Table array, and row index?

[westconn1]

maybe try simplifying your line of code, like

'This is supposed to be all one line. Also, the below line is probably what is causing the error below.

Set shtsol = Workbooks("Book1").Worksheets("Solve")
        Set psmrange = shtsol.Range(shtsol.Cells(numRows + 7, 6), shtsol.Cells(numRows + numRows + 7, netRows + 5))
        msgbox psmrange.address    ' for testing

[jdgalaway]

The setting of the PSM Range seems odd? Just what are you hoping to find here? Given that you say the error occurs in the PSMNum line of code, we need then to ascertain what the values are for the Look up value, Table array, and row index?

I'm trying to hlookup within a range of cells. I've included a copy of my spreadsheet in a simplified version for troubleshooting. I've changed some of the code since I've been trying to debug this.Book1.xlsm

The weird thing is that this code worked in a previous segment of my code. It referenced different value.

[jdgalaway]

maybe try simplifying your line of code, like

Set shtsol = Workbooks("Book1").Worksheets("Solve")
        Set psmrange = shtsol.Range(shtsol.Cells(numRows + 7, 6), shtsol.Cells(numRows + numRows + 7, netRows + 5))
        msgbox psmrange.address    ' for testing

Just tried this. The msgbox is displaying the correct range. I'm completely stumped on this one.

[jdgalaway]

It's not working because the value I'm referencing for the hlookup is a date. When I change the formatting to a number it works. Anyone have a way to keep my formatting as a date?

[westconn1]

if i converted the lookup value (psmval) to a numeric (long or double) the hlookup works without error
dates are funny like that

psmVal = CLng(Worksheets("DetailedSummary").Cells(i + 3, 34))

i see you had already figured out it was a problem looking up a date value

i also found you can use

psmVal = Worksheets("DetailedSummary").Cells(i + 3, 34).value2

i had never before figured a use for .value2

[jdgalaway]

if i converted the lookup value (psmval) to a numeric (long or double) the hlookup works without error
dates are funny like that

psmVal = CLng(Worksheets("DetailedSummary").Cells(i + 3, 34))

i see you had already figured out it was a problem looking up a date value

i also found you can use

psmVal = Worksheets("DetailedSummary").Cells(i + 3, 34).value2

i had never before figured a use for .value2

Thanks! Much appreciated.