vba 对象_worksheet的方法范围失败命名范围
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15583159/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
method range of object _worksheet failed named range
提问by DrowningInCode
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.
这不断抛出错误“对象_worksheet的方法范围失败命名范围”。错误发生在**。我知道它与命名范围有关，因为以前，当我编写单元格范围时，即。“A1：A215”它有效。我检查了名称范围，看起来不错。命名 ranged 的名称也是正确的。我尝试先激活工作簿，但仍然抛出错误。
The named ranged is:
命名范围是：
= OFFSET(DrugCodeInventory!$A, 0, 0, COUNTA(DrugCodeInventory!$A:$A)-1,1)
I only want to select the first column in my worksheet dynamically.
我只想动态选择工作表中的第一列。
采纳答案by whytheq
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")*
回答by whytheq
Kindly use the below isNameRngExistfunction which will return true when the name range "DrugCodeInventory"exist and then you can proceed with further manipulation.
请使用下面的isNameRngExist函数，该函数将在名称范围“DrugCodeInventory”存在时返回 true ，然后您可以继续进行进一步的操作。
Function isNameRngExist(myRng As String) As Boolean
On Error Resume Next
isNameRngExist = Len(ThisWorkbook.Names(TheName).Name) <> 0
End Function
