Method 'Range' of object Worksheet failed

[TheRedCardinal]

Hi all,

My macro does this:

1. Opens a new workbook from a specified file name
2. Sets Sheets(1) worksheet as variable WS3
3. Looks for the word "Date" in WS3
4. Sets a variable "WorkingRange" as the cell it found, offset by 1 row and 0 columns
5. Checks whether the month of "WorkingRange" matches the value in a named cell in another worksheet ("WS1")

Here is my code:

Set WorkingRange = WS3.UsedRange.Find("Date").Offset(1, 0)
    
    If WorkingRange <> "" Then
    
        If Month(WS3.Range("WorkingRange")) <> WS1.Range("RepMonth") Then
        
            CellA.Offset(0, 6) = "No"
            SubTest = False
            
        Else
        
            CellA.Offset(0, 6) = "Yes"
        
        End If
        
    Else
    
        CellA.Offset(0, 6) = "n/a"
    
    End If

At the first IF statement, I am getting a "Range of Object Worksheet failed" error.

When I hover over WorkingRange, I get the preview of "01/10/2022" - so it has found and offset properly.

So - which referencing protocol have I got wrong?

 

[HaHoBe]

Hi,

    If Not WorkingRange Is Nothing Then

Holger

 

[TheRedCardinal]

Hi,

    If Not WorkingRange Is Nothing Then

Holger

Hi - thanks! Sorry but I got my description wrong.

It is the SECOND if statement that is failing.

I have used your method elsewhere to test if it can be found, but here it is assuming the word is found and working from there.

So it is this line that is giving me the error:

If Month(WS3.Range("WorkingRange")) <> WS1.Range("RepMonth") Then

I need it to test for "" because it might find Date in the header but there is no data - so that is why I worked it this way.

Thanks!

 

[HaHoBe]

Hi TheRedCardinal,

if WS1.Range("RepMonth") represents a range of one cell holding a number:

Set WorkingRange = WS3.UsedRange.Find(what:="Date").Offset(1, 0)
If Not WorkingRange Is Nothing Then
  If Month(WorkingRange) <> RepMonth Then

Holger

 

[TheRedCardinal]

Hi TheRedCardinal,

if WS1.Range("RepMonth") represents a range of one cell holding a number:

Set WorkingRange = WS3.UsedRange.Find(what:="Date").Offset(1, 0)
If Not WorkingRange Is Nothing Then
  If Month(WorkingRange) <> RepMonth Then

Holger

That's the one!
Thanks