Comparing data on two workbooks: Run-time error 438 - Object does not support this propery or method

[raivyne]

Hello everyone. I am attempting to write a macro that will check data from two different workbooks and update the H column value of one of the first workbook's sheets when certain criteria is fulfilled (i.e. data for the cells in column A matches, and the data from column B of the first sheet is found in column D of the 2nd workbook).

I am getting run-time error 438 - object doesn't support this propery or method on this line of the code (in red below):

Set R1 = Intersect(sh4.Range("A:A"), sh4.Parent.UsedRange)</SPAN>

Here is the full code:

    Dim sh1 As Worksheet</SPAN>
    Dim sh4 As Worksheet</SPAN>
       
    Dim R1 As Range</SPAN>
    Dim R2 As Range</SPAN>
    Dim rc1 As Integer</SPAN>
    Dim rc2 As Integer</SPAN>
 
Set sh1 = Workbooks("RESTRC.csv").Worksheets("TICKER")</SPAN>
Set sh4 = Workbooks("MULTIDRIFT.xlsx").Worksheets("TEMP")</SPAN>
 
[COLOR=#ff0000]Set R1 = Intersect(sh4.Range("A:A"), sh4.Parent.UsedRange)[/COLOR]</SPAN>[COLOR=#ff0000]
[/COLOR] 
For rc1 = R1.Count To 1 Step -1</SPAN>
 
On Error Resume Next</SPAN>
 
Set R2 = Intersect(sh1.Range("A:A"), sh1.Parent.UsedRange)</SPAN>
           
For rc2 = R2.Count To 1 Step -1</SPAN>
 
On Error Resume Next</SPAN>
 
If InStr(1, Range(rc2, 4).Value, "*" & " " & Range(rc1, 2).Value & " " & "*") > 0 Then sh4.Range(rc1, 8).Value = "TICKER RESTRICTION"</SPAN>

Next</SPAN>

Next</SPAN>
 
On Error GoTo 0</SPAN>

I am sure that I will have the same issue for the R2 variable set up as well... I just don't understand why VBA doesn't like it. Is anyone able to help out?

 

[VBA Geek]

the parent of sh4 (worksheet) is the workbook, which does not have a property called usedrange, the worksheet does

 

[raivyne]

That makes complete sense. Thank you!

I added the parent because i was getting Run-time error '1004': method 'Intersect' of object '_global' failed. However, I was getting it on the R2 assingment which was previously located directly under the R1 assignment.

I will remove parent again and see what happens. Thanks again!