vba 单元格范围中的应用程序定义或对象定义错误 Visual Basic
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16550958/
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
application-defined or object-defined error in cell range visual basic
提问by user2351095
Having some problems with the code below. I'm getting an Application-defined or object-defined error in the initiation of a second for loop. The format of the second loop range is what seems to be causing the problem. Removing the Sheets() object gets rid of the error, but then the script reads from the wrong worksheet, and doesn't return any data.
下面的代码有一些问题。我在启动第二个 for 循环时遇到应用程序定义或对象定义的错误。第二个循环范围的格式似乎是导致问题的原因。删除 Sheets() 对象可以消除错误，但是脚本从错误的工作表中读取，并且不返回任何数据。
The goal of this code is to loop over a vertical array of data, and then if a match to a selection from a dropdown is found, it loops over a horizontal array of data and returns a color change if it finds a 'Yes' value.
这段代码的目标是遍历垂直数据数组，然后如果找到与下拉列表中选择的匹配项，则循环遍历水平数据数组，如果找到“是”值，则返回颜色更改.
If Not Intersect(Target, Range("countryProductCell")) Is Nothing Then
lastcolumn = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count
Dim cellRow As Integer
cellRow = Target.Row
Dim defaultCellColumn As Integer
defaultCellColumn = 4
i = 5
j = 1
k = 1
If Not Cells(cellRow, defaultCellColumn).Value = "(Select Title)" Then
For Each countryCell In Range(Cells(cellRow, defaultCellColumn + 1), Cells(cellRow, lastcolumn))
If countryCell.Value = "Use Default" Then
countryCell.Interior.ColorIndex = 3
End If
Next
For Each nameCell In Sheets("Active Product Catalog").Range("ProductNames")
If nameCell.Value = Cells(cellRow, defaultCellColumn).Value Then
'Error on the line below!
For Each purchaseableCell In Sheets("Active Product Catalog").Range(Cells(nameCell.Row, 10), Cells(nameCell.Row, 27))
If purchaseableCell.Value = "Yes" Then
'If Purchaseable, Change Color
Sheets("Home Template").Cells(cellRow, defaultCellColumn + j).Interior.ColorIndex = 35
End If
j = j + 1
Next
End If
k = k + 1
Next
ElseIf Cells(cellRow, defaultCellColumn).Value = "(Select Title)" Then
If Target.Value = "(Select Title)" Then
Target.Interior.Color = Cells(Target.Row, Target.Column - 1).Interior.Color
For Each countryCell In Range(Cells(cellRow, defaultCellColumn + 1), Cells(cellRow, lastcolumn))
If countryCell.Value = "Use Default" Then
countryCell.Interior.ColorIndex = 2
End If
i = i + 1
Next
ElseIf Target.Value = "Use Default" Then
Target.Interior.ColorIndex = 2
ElseIf Application.VLookup(ActiveSheet.Cells(cellRow, Target.Column), Sheets("Active Product Catalog").Range("E:AK"), Target.Column, False) = "Yes" Then
Target.Interior.ColorIndex = 35
ElseIf Not Application.VLookup(ActiveSheet.Cells(cellRow, Target.Column), Sheets("Active Product Catalog").Range("E:AK"), Target.Column, False) = "Yes" Then
Target.Interior.ColorIndex = 3
End If
End If
End If
回答by steveo40
You need to qualify the Cells references in your code. The reason it is failing is that you are using two Cell references from one sheet (the active sheet) and asking VBA to define a range in another sheet (Active Product Catalog). Try something like this:
您需要限定代码中的单元格引用。失败的原因是您正在使用一张工作表（活动工作表）中的两个单元格引用，并要求 VBA 在另一张工作表（活动产品目录）中定义一个范围。尝试这样的事情：
Sheets("Active Product Catalog").Range(Sheets("Active Product Catalog").Cells(nameCell.Row, 10), Sheets("Active Product Catalog").Cells(nameCell.Row, 27))
You'll probably find it a bit easier to read if you create a worksheet object or use a With statement.
如果您创建工作表对象或使用 With 语句，您可能会发现它更易于阅读。
