CODEWITHSUNDEEP
vbaexcel
kmalik
kmalik

Reputation: 17

Object doesn't support this property or method

I am essentially trying to match a series of cells from 2 worksheets and obtained values from a corresponding column.

When I run this code, I get an error:

object doesn't support this property or method

My Code

Sub Macro2()

Dim rowcount As Integer
Dim target As Variant

rowcount = Range("E2", Range("E2").End(xlDown)).Count
For i = 1 To rowcount + 1
    target = Application.Match(ActiveSheet.Cells(i, 6) & "-" & Cells(i, 5) & "-" & Cells(i, 4) & "-" & Cells(i, 3), Worksheets(14).Range("A6:A3000"), 0)
    If ActiveSheet.Cells(i, 6) & "-" & Cells(i, 5) & "-" & Cells(i, 4) & "-" & Cells(i, 3) = _
        ActiveSheet.Cells(i + 1, 6) & "-" & Cells(i + 1, 5) & "-" & Cells(i + 1, 4) & "-" & Cells(i + 1, 3) Then
        ActiveSheet.Cells(i, 17) = Worksheets(14).target.Offset(0, 10)
    End If
Next i

End Sub

Upvotes: 0

Views: 895

Answers (2)

YowE3K
YowE3K

Reputation: 23994

target is not a Property or Method of a Worksheets object.

I believe you need to change Worksheets(14).target.Offset(0, 10) to Worksheets(14).Range("A5").Offset(target, 10).

You should also be consistent in your coding. In your code you have things like ActiveSheet.Cells(i + 1, 6) & "-" & Cells(i + 1, 5) ..., where you specifically qualify Cells(i + 1, 6) to be on ActiveSheet but allow Cells(i + 1, 5) to default to being on the ActiveSheet. Although it works, it will get very confusing if you ever need to reread your code later on.

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33692

If you want to find the Row count, you need to use the syntax: rowcount = Range(Range("E2"), Range("E2").End(xlDown)).Rows.Count , also it's better to use Long than Integer.

Also, you need to trap a possible error when unable to find a successful match with the Application.Match function, do it by using If Not IsError(target) Then.

Note: try to avoid using ActiveSheet, instead use fully qualified worksheet, by using Worksheets("YourSheetName") in your code.

Code

Sub Macro2()

Dim rowcount As Long
Dim target As Variant

rowcount = Range(Range("E2"), Range("E2").End(xlDown)).Rows.Count
For i = 1 To rowcount + 1
    target = Application.Match(ActiveSheet.Cells(i, 6) & "-" & Cells(i, 5) & "-" & Cells(i, 4) & "-" & Cells(i, 3), Worksheets(14).Range("A6:A3000"), 0)
    If Not IsError(target) Then ' successful Match
        If ActiveSheet.Cells(i, 6) & "-" & Cells(i, 5) & "-" & Cells(i, 4) & "-" & Cells(i, 3) = _
            ActiveSheet.Cells(i + 1, 6) & "-" & Cells(i + 1, 5) & "-" & Cells(i + 1, 4) & "-" & Cells(i + 1, 3) Then
            ActiveSheet.Cells(i, 17) = Worksheets(14).target.Offset(0, 10)
        End If
    Else
        MsgBox "Unable to find a Match !"
    End If
Next i

End Sub

Upvotes: 1

Related Questions