RangeFind Produces Error 424

[Turk182]

Hello. Can someone please help. I am not sure why I'm getting this error: "Runtime error 424 Object Required”.

Here is the VBA code:

Sub RangeFind()

Dim lastRow As Long, lastColumn As Long

lastRow = shJagged.Cells.Find(What:="*" _

, LookIAt:=xlPart _

, LookIn:=xlFormulas _

, searchorder:=xlByRows _

, searchdirection:=xlPrevious).Row

lastColumn = shJagged.Cells.Find(What:="*" _

, LookIAt:=xlPart _

, LookIn:=xlFormulas _

, searchorder:=xlByColumns _
, searchdirection:=xlPrevious).Column

rg.Select

End Sub

Thx.

 

[dmt32]

Hi.
you have a few errors in your code including missing a reference to worksheet object shJagged

Sub RangeFind()
    Dim shJagged    As Worksheet
    Dim lastRow     As Long, lastColumn As Long
   
    Set shJagged = ThisWorkbook.Worksheets("Jagged")
   
    lastRow = shJagged.Cells.Find(What:="*", LookAt:=xlPart, LookIn:=xlFormulas, _
    searchorder:=xlByRows, searchdirection:=xlPrevious).Row
   
    lastColumn = shJagged.Cells.Find(What:="*", LookAt:=xlPart, LookIn:=xlFormulas, _
    searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
   
    shJagged.Cells(lastRow, lastColumn).Select

End Sub

As you are selecting the range I am assuming sheet Jagged is the activesheet.

Dave

 

[Turk182]

Hi.
you have a few errors in your code including missing a reference to worksheet object shJagged

Sub RangeFind()
    Dim shJagged    As Worksheet
    Dim lastRow     As Long, lastColumn As Long
  
    Set shJagged = ThisWorkbook.Worksheets("Jagged")
  
    lastRow = shJagged.Cells.Find(What:="*", LookAt:=xlPart, LookIn:=xlFormulas, _
    searchorder:=xlByRows, searchdirection:=xlPrevious).Row
  
    lastColumn = shJagged.Cells.Find(What:="*", LookAt:=xlPart, LookIn:=xlFormulas, _
    searchorder:=xlByColumns, searchdirection:=xlPrevious).Column
  
    shJagged.Cells(lastRow, lastColumn).Select

End Sub

As you are selecting the range I am assuming sheet Jagged is the activesheet.

Dave

 

[Turk182]

Thanks Dave; adjustments made!
The first error is gone, but this is the new error: “Runtime error 91: object variable with block not set

lastRow = shJagged.Cells.Find(What:="*", LookAt:=xlPart, LookIn:=xlFormulas, _
searchorder:=xlByRows, searchdirection:=xlPrevious).Row

...any idea what in the above code prompts that error?

Thx,
Anthony

 

[dmt32]

Hi,
I suspect that your worksheet contains no data

Try using my updated version of the code & see if helps

Sub RangeFind()
    Dim shJagged        As Worksheet
    Dim RowCol(1 To 2)  As Variant
    Dim i               As Long
    
    Set shJagged = ThisWorkbook.Worksheets("Jagged")
    
    On Error Resume Next
    For i = xlRows To xlColumns
        With shJagged.Cells.Find(What:="*", After:=shJagged.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, _
                            searchorder:=i, searchdirection:=xlPrevious, MatchCase:=False)
            RowCol(i) = Choose(i, .Row, .Column)
        If Err > 0 Then RowCol(i) = 1
        End With
    Next i
     On Error GoTo 0
     
     shJagged.Cells(RowCol(xlRows), RowCol(xlColumns)).Select
End Sub

Dave

 

[Turk182]

Hi,
I suspect that your worksheet contains no data

Try using my updated version of the code & see if helps

Sub RangeFind()
    Dim shJagged        As Worksheet
    Dim RowCol(1 To 2)  As Variant
    Dim i               As Long
   
    Set shJagged = ThisWorkbook.Worksheets("Jagged")
   
    On Error Resume Next
    For i = xlRows To xlColumns
        With shJagged.Cells.Find(What:="*", After:=shJagged.Range("A1"), LookIn:=xlFormulas, LookAt:=xlPart, _
                            searchorder:=i, searchdirection:=xlPrevious, MatchCase:=False)
            RowCol(i) = Choose(i, .Row, .Column)
        If Err > 0 Then RowCol(i) = 1
        End With
    Next i
     On Error GoTo 0
    
     shJagged.Cells(RowCol(xlRows), RowCol(xlColumns)).Select
End Sub

Dave

Thanks Dave. Both errors are gone now!

 

[dmt32]

Thanks Dave. Both errors are gone now!

welcome glad resolved & appreciate feedback

Dave

 

[Turk182]

welcome glad resolved & appreciate feedback

Dave

HI Dave. Please heip. Both errors definitely are gone (Thx again), but a different type of issue arose:

I THOUGHT the code was going to select

all cells from the last occupied row up,
and from the last occupied column all the way over to the leftmost column.

What it is selecting is the cell at the bottom right (the intersection of
the Last row and last column)….

How can I get the whole range selected?

Thx.

 

[dmt32]

How can I get the whole range selected?

Thx.

If you are using my updated version of the code change this line

shJagged.Cells(RowCol(xlRows), RowCol(xlColumns)).Select

for this

shJagged.Cells(1, 1).Resize(RowCol(xlRows), RowCol(xlColumns)).Select

if using your version of code then its this

shJagged.Cells(1, 1).Resize(lastRow, lastColumn).Select

either version should select from cell A1

Dave

 

[Turk182]

If you are using my updated version of the code change this line

shJagged.Cells(RowCol(xlRows), RowCol(xlColumns)).Select

for this

shJagged.Cells(1, 1).Resize(RowCol(xlRows), RowCol(xlColumns)).Select

if using your version of code then its this

shJagged.Cells(1, 1).Resize(lastRow, lastColumn).Select

either version should select from cell A1

Dave

Hi Dave,
Unfortunately, I get "run-time error 91" again.