vba 为什么我会收到运行时错误 424 Object Required？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18088009/
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
Why am I getting Run-time Error 424 Object Required?
提问by user2657997
I am fairly new to VBA. I'm trying to write a program to run through large amounts of Part Numbers of various formats and categorize such part numbers, like so:
我对 VBA 相当陌生。我正在尝试编写一个程序来运行大量各种格式的零件编号并对这些零件编号进行分类，如下所示：
12A3-4
1B-4
2B-6
A12B
And then have my program find all the formats of these types and return and count them, like so: (Note numbers are now represented by #)
然后让我的程序找到这些类型的所有格式并返回并计算它们，如下所示：（注意数字现在由 # 表示）
##A# 1
#B 2
A##B 1
But I am getting a runtime error that I cannot seem to source.
但是我收到了一个我似乎无法找到的运行时错误。
My program is as follows. There might be other errors.
我的程序如下。可能还有其他错误。
Sub CheckPartNumbers()
' Select cell A2, where data begins
Range("A2").Select
' Declare variable for cell location of our output
Dim Cell As Range
Set Cell = ActiveSheet.Range("C2")
' Set Do loop to stop when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
' Initialize vairable of type string to ""
Dim partsFormat As String
partsFormat = ""
' Run through each character of row
For i = 1 To Len(ActiveCell.Value)
Dim thisChar As String
thisChar = Mid(ActiveCell.Value, i, 1)
' if thisChar is a letter
If IsLetter(thisChar) Then
partsFormat = partsFormat & thisChar
' if thischar is a number
ElseIf IsNumeric(thisChar) Then
partsFormat = partsFormat & "#"
' if dash
ElseIf thisChar = "-" And (Len(ActiveCell.Value) - Len(Replace(ActiveCell.Value, "-", ""))) > 1 Then
partsFormat = partsFormat & thisChar
Else
i = Len(ActiveCell.Value)
End If
Next i
' Check if partsFormat already exists in results with Match
Dim myLocation As Range
Set myLocation = Application.Match(partsFormat, Range("C2:D1"))
' If no, result will give error, so add partsFormat and make count 1
If IsError(myLocation) Then
Range(Cell) = partsFormat
Range(Cell).Offset(0, 1) = 1
Cell = Cell.Offset(1, 0)
' If yes, add 1 to appropriate cell
Else
myLocation.Offset(0, 1) = myLocation.Offset(0, 1).Value + 1
End If
' Run through next row
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Any help is appreciated!
任何帮助表示赞赏！
EDIT: I had quite a few errors, so this chunk of my code is updated:
编辑：我有很多错误，所以我的这段代码更新了：
Dim myLocation As Variant
myLocation = Application.Match(partsFormat, Range("C1").EntireColumn)
' If no, result will give error, so add partsFormat and make count 1
If IsError(myLocation) Then
Cell = partsFormat
Cell.Offset(0, 1) = 1
Cell = Cell.Offset(1, 0)
' If yes, add 1 to appropriate cell
Else
'myLocation.Offset(0, 1) = myLocation.Offset(0, 1).Value + 1
End If
回答by Doug Glancy
The 424 error is caused by these two lines:
424错误是由这两行引起的：
Dim myLocation As Range
Set myLocation = Application.Match(partsFormat, Range("C2:D1"))
You've declared myLocation as a Range. You are then trying to set it to a number, which is what MATCHreturns, not a Range. The Object that's required is a range.
您已将 myLocation 声明为Range. 然后你试图将它设置为一个数字，这是MATCH返回的，而不是一个范围。所需的对象是一个范围。
EDIT:
编辑：
In order to just Count the occurrences of partsFormatin Column C, use something like this:
为了只计算partsFormat列 C 中的出现次数，请使用以下内容：
Dim partsFormat As String
Dim partsFormatCount As Long
partsFormat = "Part"
partsFormatCount = Application.WorksheetFunction.CountIf(Range("C:C"), partsFormat)
You'll obviously have to insert that into the right places in your code.
显然，您必须将其插入代码中的正确位置。
