Application-defined or object defined error

Question

All,

I am receiving the error "Application defined or object defined error" for a private sub that I have written. The code is below:

Private Sub CommandButton3_Click()

Dim MyLastRow As Long
Dim i As Long
Dim cellmatch

'Find the last row
MyLastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Define our comparison
cellmatch = Application.Match(Cells(i, "A").Value, Range(Cells(i, "C")).Value, 0)

'Compare Raw Data cell to Stock column and find a match
For i = 2 To MyLastRow
    If IsError(cellmatch) Then
        Cells(i, 2) = "Not in Stock"
    Else
        Cells(i, 2) = "-"
    End If
Next i

End Sub

I have tried several things I found on the forums such us specifying the worksheet

Application.WorksheetFuncion.Match.....

I've also tried point to the cell or range such as:

Range(.Cells(i,"C"))....

or .Match(.Cells(i,"A"))...

But I keep getting the same error. All of this is happening on the same sheet and I'm not trying to do anything fancy like copying. I am simply asking if a match is NOT found, then label as such, else, label it with a dash (done like this for clarity). I am sure it's something very simple but I am new to coding in VBA. Any help is much appreciated.

Thanks!

Also your match function is just comparing one cell to another, but it looks like it's supposed to be checking a column rather than a cell... — SierraOscar
– SierraOscar, Commented Jun 1, 2016 at 13:30
You may not get error if you change your corresponding line to this ~cellmatch = Application.Match(Cells(i, "A").Value, Worksheets("Sheet1").Columns(3), 0)~. I think @Macro Man has also pointed to the same shortcoming in your coding. — skkakkar
– skkakkar, Commented Jun 1, 2016 at 14:22
@MacroMan yes, it is supposed to be checking one cell against a column. It should be asking if the there is a cell in the column that matchs the active cell. If not, then it states so. Otherwise, it will state it as a '-'. — Rumble1701a
– Rumble1701a, Commented Jun 2, 2016 at 15:55
@micstr Oh Duh! Thanks. Yeah, that should have been in my FOR loop. :P I'll make that correction. — Rumble1701a
– Rumble1701a, Commented Jun 2, 2016 at 15:56

skkakkar · Accepted Answer · 2016-06-01 16:11:47Z

1

Your code requires change of this code line.

 cellmatch = Application.Match(Cells(i, "A").Value, Range(Cells(i, "C")).Value, 0)

TO

 'Adjust Sheetname as per your requirements instead of "Sheet1"
     cellmatch = Application.Match(Cells(i, "A").Value, Worksheets("Sheet1").Columns(3), 0)

EDIT

Main problem is coming in your program because of the following code fragment.

Range(Cells(i, "C")).Value

If we refer to MSDN Documenation Range.Cells Property (Excel)

It mentions exammples of correct syntax of usage. Typical example is

Set r = Range("myRange") 
For n = 1 To r.Rows.Count 
    If r.Cells(n, 1) = r.Cells(n + 1, 1) Then 
        MsgBox "Duplicate data in " & r.Cells(n + 1, 1).Address 
    End If 
Next n

So it translates to Range("myRange").Cells(n,1)
and not

Range(Cells(i, "C"))

It will give correct results as shown in the snapshot.

edited Jun 1, 2016 at 16:11

answered Jun 1, 2016 at 15:13

skkakkar

2,8482 gold badges20 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ralph · Accepted Answer · 2016-06-01 13:43:44Z

I believe this is what you are looking for:

Option Explicit

Private Sub CommandButton3_Click()

Dim lngRow As Long
Dim rngFound As Range
Dim lngLastRow As Long
Dim shtCurrent As Worksheet

'Set the sheet to work on
Set shtCurrent = ThisWorkbook.Worksheets("Sheet1")

With shtCurrent
    'Find the last row
    lngLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    'Exit if the last row is 2 or smaller
    If lngLastRow <= 2 Then
        MsgBox "Nothing to compare!" & Chr(10) & "Aborting..."
        Exit Sub
    End If

    'Compare Raw Data cell to Stock column and find a match
    For lngRow = 2 To lngLastRow
        'Only compare if there is something in column A to compare
        If .Cells(lngRow, "A").Value2 <> vbNullString Then
            'This is the actual MATCH / FIND
            Set rngFound = .Range("C:C").Find(What:=.Cells(lngRow, "A").Value2, LookIn:=xlValues, LookAt:=xlWhole)
            'Evaluate the result of the FIND = rngFound
            If rngFound Is Nothing Then
                .Cells(lngRow, 2).Value2 = "Not in Stock"                           'not found
            Else
                .Cells(lngRow, 2).Value2 = "In stock in row " & rngFound.Row        'found
            End If
        End If
    Next lngRow
End With

End Sub

Let me know if you have and problems / questions.

Collectives™ on Stack Overflow

Application-defined or object defined error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related