vba VBA方法'对象范围'_Worksheet在运行代码时突然出现失败？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20008033/
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
VBA method 'range of object' _Worksheet failed suddenly coming up when running code?
提问by SilverShotBee
This code used to run fine, then all of a sudden it started failing on "method 'range of object' _Worksheet failed"? ' I'm not an expert with VBA, but I can get myself around it. Is there something I'm missing?
这段代码过去运行良好，然后突然间它开始在“方法'对象范围'_工作表失败”上失败？'我不是 VBA 专家，但我可以解决它。有什么我想念的吗？
Public Sub Run_Count_Click()
'// Set Ranges
Dim Cr_1, CR1_range, _
Cr_2, CR2_range, _
Cr_3, CR3_range, _
Cr_4, CR4_range, _
Cr_5, CR5_range _
As Range
'// Set Integers
Dim CR1, V1, CR1_Result, _
CR2, V2, CR2_Result, _
CR3, V3, CR3_Result, _
CR4, V4, CR4_Result, _
CR5, V5, CR5_Result, _
total_result, _
total_result2, _
total_result3, _
total_result4, _
total_result5 _
As Integer
'Set Strings
Dim V_1, V_2, V_3, V_4, V_5 As String
Dim ws As Worksheet
Set ws = Worksheets("database")
'// Get Criteria From Form And Search Database Headers
Set Cr_1 = ws.Cells.Find(What:=Me.Count_Criteria_1.Value, After:=Cells(1, 1), MatchCase:=False)
If Not Cr_1 Is Nothing Then
CR1 = Cr_1.Column '//Set CR1 as the Column in which the Criteria Header was found
Else
MsgBox "Criteria 1 Has Not Been Found In The Database. Report Has Failed To Generate"
Exit Sub
End If
'// Get Variable Value From Form And Set Shortcode
V_1 = Me.Criteria_1_Variable.Value
Set CR1_range = ws.Range(Cells(5, CR1), Cells(Rows.count, CR1))
CR1_Result = Application.CountIf(CR1_range, V_1)
If Me.Count_Criteria_2 = "Any" Then
Me.Count_Result.visible = True
Me.Count_Result.Value = "Based On Your Search Criteria Of:" & vbNewLine & _
"How many occurences of [" & Me.Criteria_1_Variable.Value & "] in the category [" & Me.Count_Criteria_1.Value & _
"] have occured between the dates..." & vbNewLine & vbNewLine & "The Results Are: " & CR1_Result
Exit Sub
The code fails on this line:
代码在这一行失败：
Set CR1_range = ws.Range(Cells(5, CR1), Cells(Rows.count, CR1))
Thanks in advance guys
提前谢谢各位
回答by Joe Laviano
The Cells are being referenced from the activesheet, not "database".
单元格是从活动表中引用的，而不是“数据库”。
Set CR1_range = ws.Range(ws.Cells(5, CR1), ws.Cells(ws.Rows.count, CR1))
回答by azar
Excel VBA method range of object _global failed?
对象_global的Excel VBA方法范围失败？
Sub kar()
Dim i, j
For i = 8 To 15
For j = 9 To 15
If (Range("ji") = Range("jj") And Range("gi") = "????" And Range("gj") = "????" And (Abs(Range("ki") - Range("kj") <= 50))) Then
Range("ui") = -(Range("ki") - Range("kj")) * hi
Range("uj") = -(Range("ki") - Range("kj")) * hj
End If
If (Range("ji") = Range("jj") And Range("gi") = "????" And Range("gj") = "????" And (Abs(Range("ki") - Range("kj")) <= 50)) Then
Range("uj").Value = -(Range("ki") - Range("kj")) * hi
Range("ui").Value = -(Range("ki") - Range("kj")) * hj
End If
Next j
Next i
End Sub
回答by JeffSea
I ran into the same error message but the cause was different for me. I am only referencing the active worksheet. Apparently the default ReferenceStyle was changed for a few of my users that all have the same VBA logic. Not sure if this was a result of application updates or if multiple users managed to make the same change. Debugging the logic I found that the PrintArea representation would no longer convert to a Range.
我遇到了相同的错误消息，但对我来说原因不同。我只引用活动工作表。显然，我的一些用户的默认 ReferenceStyle 已更改，这些用户都具有相同的 VBA 逻辑。不确定这是应用程序更新的结果还是多个用户设法进行了相同的更改。调试逻辑我发现 PrintArea 表示将不再转换为 Range。
wS.Range(wS.PageSetup.PrintArea).Address
The PrintArea value was populated but shown in a format that unfamiliar to me. Turns out it was R1C1 style instead of the default A1 style cell reference. I found another post that referenced changing the reference style in their user settings but I did not want to force that change on my users. I decided to set the reference style for this workbook each time it was opened. I hooked into Workbook_Open and used xlA1.
PrintArea 值已填充，但以我不熟悉的格式显示。原来它是 R1C1 样式而不是默认的 A1 样式单元格引用。我发现另一篇文章提到在他们的用户设置中更改参考样式，但我不想将这种更改强加给我的用户。我决定在每次打开此工作簿时为其设置参考样式。我连接到 Workbook_Open 并使用了 xlA1。
Application.ReferenceStyle = xlA1
