VBA - Run-time Error '1004': Application-defined or object defined error

[raivyne]

Hello, I am working on coding part of a macro that checks for the occurance of a string in another string, and if a match is found it needs to change the I cell value of the relevant row.

I am getting Run-time Error '1004': Application-defined or object defined error on the red portion of the following code (the error handling is intentionally disabled at the moment & the missing Dim statements for R1, R2, rc1, rc2, sh1 and sh4 are elsewhere in the macro):

Dim anum As String
Dim tick As String
Dim note As String
Dim match As String

Set R2 = Intersect(sh4.Range("A:A"), sh4.UsedRange)

For rc2 = R2.Count To 2 Step -1
    
    anum = sh4.Cells(rc2, 1).Value
    tick = sh4.Cells(rc2, 2).Value
 
    sh1.Activate
    ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=anum
    
    Set R1 = Intersect(sh1.Range("F:F"), sh1.UsedRange)
    
    R1.Select
    Selection.Copy
    sh4.Activate
    Range("J1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Set R1 = Intersect(sh4.Range("J2:J1048576"), sh4.UsedRange)
    
    For rc1 = R1.Count To 1 Step -1
                            
        match = sh4.Cells(rc1, 10).Value
                            
        If InStr(match, tick) > 0 Then [B][COLOR=#ff0000]ActiveSheet.Range(rc2, 8) = "TICKER RESTRICTION"
[/COLOR][/B]     
        'On Error Resume Next
                            
    Next
    
        'On Error Resume Next
Next

I tried using a variable to update the value of the cell. This stopped the error, but the I-cell value doesn't get updated when I know each row of my test spreadsheet should return a value > 0. Here is that code:

Dim anum As String
Dim tick As String
Dim note As String
Dim match As String

Set R2 = Intersect(sh4.Range("A:A"), sh4.UsedRange)

For rc2 = R2.Count To 2 Step -1
    
    anum = sh4.Cells(rc2, 1).Value
    tick = sh4.Cells(rc2, 2).Value
    note = sh4.Cells(rc2, 8).Value
    
    sh1.Activate
    ActiveSheet.Range("A1").AutoFilter Field:=1, Criteria1:=anum
    
    Set R1 = Intersect(sh1.Range("F:F"), sh1.UsedRange)
    
    R1.Select
    Selection.Copy
    sh4.Activate
    Range("J1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
    Set R1 = Intersect(sh4.Range("J2:J1048576"), sh4.UsedRange)
    
    For rc1 = R1.Count To 1 Step -1
                            
        match = sh4.Cells(rc1, 10).Value
                            
        If InStr(match, tick) > 0 Then note = "TICKER RESTRICTION"
        
        'On Error Resume Next
                            
    Next
    
        'On Error Resume Next
Next

Can anyone shed some light on what I am doing wrong? Thanks in advance!

 

[Andrew Poulsom]

You need Cells rather than Range.

 

[raivyne]

That did it. Thanks ever so much!