method range of object _worksheet failed named range

Question

Private Sub Submit_Click()
Application.ScreenUpdating = False

Dim rangeForCode As range, rngLookupRange As range

Dim row As Integer, stock As Integer
Dim result As Integer
Dim drugCodePC As Integer
Dim qty As Integer
Dim ws As Worksheet

drugCodePC = CInt(DrugCode2.Value)
qty = CInt(Quantity.Value)

'Populating the drug name
Set ws = Worksheets("Drug Record")
ws.Select

*Set rangeForCode = ws.range("DrugCodeInventory")*
row = Application.WorksheetFunction.Match(drugCodePC, rangeForCode, 1)
Set rngLookupRange = ws.range("Inventory")
stock = Application.WorksheetFunction.VLookup(drugCodePC, rngLookupRange, 3, False)
result = stock + qty
'MsgBox (row)
ws.Cells(row + 1, 3).Value = result

Application.ScreenUpdating = True
Unload PurchaseForm 
End Sub

This keeps throwing the error "method range of object _worksheet failed named range". The error occurs at the **. I know it has something to do with the named ranged because previously, when i wrote the range of cells ie. "A1:A215" it works. I've checked the name range and it looks right. The name of the named ranged is also correct. I've tried to activate the workbook first but the error is still thrown.

The named ranged is:

= OFFSET(DrugCodeInventory!$A$2, 0, 0, COUNTA(DrugCodeInventory!$A:$A)-1,1)

I only want to select the first column in my worksheet dynamically.

Answer 1

If you run this in the Immediate window does it work?

application.Goto Worksheets("Drug Record").range("DrugCodeInventory")

If it doesn't run then try deleting the named range and creating a new one.

Please also try explicitly qualifying this section of your code:

Dim ws As Excel.Worksheet '<added full qualification here

drugCodePC = CInt(DrugCode2.Value)
qty = CInt(Quantity.Value)

'Populating the drug name
Set ws = Excel.thisworkbook.Worksheets("Drug Record") '<added full qualification here
ws.Select

*Set rangeForCode = ws.range("DrugCodeInventory")*

Answer 2

Kindly use the below isNameRngExist function which will return true when the name range "DrugCodeInventory" exist and then you can proceed with further manipulation.

Function isNameRngExist(myRng As String) As Boolean
    On Error Resume Next
    isNameRngExist = Len(ThisWorkbook.Names(TheName).Name) <> 0
End Function