VLookup() Run-Time Error '1004'

Getting Run-Time Error '1004'
Unable to get the Vlookup property of the WorksheetFunction Class

I am not sure why. In Debug mode the variables are all correct.

 Dim drg_code As String,  disch_date As Date, facility As Variant, admit_type As String
 Dim drg_value As Variant, look_up_sheet As Worksheet, ds As Worksheet, data_array As Variant, drg_descrip As Variant
 Dim dyear As Variant, geo_zip As Variant, client_list As Worksheet, allowance As Variant, conc As Variant
 Dim Arrays As Worksheet

Set look_up_sheet = ActiveWorkbook.Sheets("Sheet8")
facility = look_up_sheet.Range("C5")
Set client_list = ActiveWorkbook.Sheets("CLIENT LIST")
geo_zip = Application.WorksheetFunction.vlookup(facility, client_list.Range("A1:E16"), 2, False)

disch_date = Application.InputBox(prompt:="Enter the Discharge Date", Title:="Discharge Date", Type:=2)
    
    If Year(disch_date) = 2009 Then
        If disch_date <= 39993 Then
            dyear = "010109"
        Else
            dyear = "063009"
        End If
    Else
        dyear = Year(disch_date)
    End If

admit_type = look_up_sheet.Range("F12")
drg_code = Application.InputBox(prompt:="Enter the DRG Code", Title:="DRG Code", Type:=1)
conc = dyear & admit_type & geo_zip
Set Arrays = ActiveWorkbook.Sheets("Arrays")
data_array = Application.WorksheetFunction.vlookup(conc, Arrays.Range("A1:E393"), 5, False)
Set ds = ActiveWorkbook.Sheets(dyear)
drg_value = Application.WorksheetFunction.vlookup(drg_code, ds.Range(data_array), 3, False)

UNABLE TO ATTACH FILE AS THEY'RE HUGE.

this was previous code that worked.

Dim drg_value As Variant, look_up_sheet As Worksheet, ds As Worksheet, data_sheet As Variant, data_array As Variant

Set look_up_sheet = ActiveWorkbook.Sheets("Sheet8")
data_sheet = look_up_sheet.Range("H3")
Set ds = ActiveWorkbook.Sheets(data_sheet)
data_array = look_up_sheet.Range("H2")
drg_value = Application.WorksheetFunction.vlookup(look_up_sheet.Range("F13"), ds.Range(data_array), 3, False)
look_up_sheet.Range("H4").Value = drg_value

Hi

worked for me. Are you sure your sheet names are correct, and that there is relevant data to find?

rylo

Yes, the sheets are name correctly. That was the first thing I checked. Several times.
There is a significant amount of data to find.

You checked the code in the top section? The second set of code is what I had working previously. I am trying to get away from hvaing to read values from the spreadsheet as I am having the user enter data through InputBox.

I was able to resolve the problem by defining drg_code as a Variant instead of the String it was defined as.