PDA

View Full Version : [SOLVED] WorksheetFunction.HLookUP 1004 Error



jdgalaway
08-05-2014, 07:18 PM
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
08-06-2014, 03:08 AM
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
08-06-2014, 04:26 AM
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
08-06-2014, 04:40 AM
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.12062

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

jdgalaway
08-06-2014, 05:19 AM
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
08-06-2014, 05:26 AM
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
08-06-2014, 05:41 AM
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
08-06-2014, 05:52 AM
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.