CODEWITHSUNDEEP
excelexcel-2010vba
Rumble1701a
Rumble1701a

Reputation: 23

Application-defined or object defined error

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!

Upvotes: 1

Views: 2304

Answers (2)

skkakkar
skkakkar

Reputation: 2828

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.

Snapshot showing results

Upvotes: 1

Ralph
Ralph

Reputation: 9444

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.

Upvotes: 0

Related Questions