CODEWITHSUNDEEP
vbaexcelruntime-errorvlookup
Josh Hudson
Josh Hudson

Reputation: 103

Run0time error '438'

I have the below line that is receiving a "Object doesn't support the property or method error, but i am not seeing any issues.

Dim compliance As Worksheet
Dim report As Worksheet
Dim completeList As Worksheet

Sub getcompliance()
    Dim i As Long
    Dim n As Long
    Dim Source As String
    Set compliance = ActiveWorkbook.Worksheets("Compliance")
    Set report = ActiveWorkbook.Worksheets("Report")
    For i = 3 To report.UsedRange.Rows.Count
      For n = 2 To compliance.UsedRange.Rows.Count
        report(i, 19) = Application.WorksheetFunction.VLookup(report("i, 3"), compliance("A1:AC2400"), 29, False)
      Next n
    Next i
End Sub

Upvotes: 2

Views: 167

Answers (2)

YowE3K
YowE3K

Reputation: 23974

Your line saying

report(i, 19) = Application.WorksheetFunction.VLookup(report("i, 3"), compliance("A1:AC2400"), 29, False)

should probably say

report.Cells(i, 19) = Application.WorksheetFunction.VLookup(report.Cells(i, 3), compliance.Range("A1:AC2400"), 29, False)

but, if so, why are you doing that in a For n loop?

Perhaps you mean your code to be:

Dim compliance As Worksheet
Dim report As Worksheet
Dim completeList As Worksheet

Sub getcompliance()
    Dim i As Long
    Set compliance = ActiveWorkbook.Worksheets("Compliance")
    Set report = ActiveWorkbook.Worksheets("Report")
    For i = 3 To report.UsedRange.Rows.Count
        report.Cells(i, 19) = Application.WorksheetFunction.VLookup(report.Cells(i, 3), compliance.Range("A1:AC" & compliance.UsedRange.Rows.Count), 29, False)
        'Or, simply using the full columns:
        'report.Cells(i, 19) = Application.WorksheetFunction.VLookup(report.Cells(i, 3), compliance.Range("A:AC"), 29, False)
    Next i
End Sub

Upvotes: 3

A.S.H
A.S.H

Reputation: 29332

'      vvvvv                                          vvvvvvvvvvvvv
report.Cells(i, 19) = WorksheetFunction.VLookup(report.Cells(i, 3), compliance.Range("A1:AC2400"), 29, False)
'                                                                              ^^^^^^

Upvotes: 2

Related Questions