CODEWITHSUNDEEP
excelvba
user2657997
user2657997

Reputation: 1

Why am I getting Run-time Error 424 Object Required?

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:

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

Upvotes: 0

Views: 41693

Answers (1)

Doug Glancy
Doug Glancy

Reputation: 27488

The 424 error is caused by these two lines:

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 MATCH returns, not a Range. The Object that's required is a range.

EDIT:

In order to just Count the occurrences of partsFormat in Column C, use something like this:

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.

Upvotes: 3

Related Questions