My range creation macro crashes when there's nothing in range and it's a bummer.

[jmpatrick]

Good afternoon.

I have some code sourced from powerspreadsheets.com that creates a named range called FloorPlanRequestsFormulas.

It works as intended when there is data in the target cells, but gives this error when there is none:

Run-time error '91':

Object variable or With block variable not set

More specifically, it doesn't seem to like this line:

myLastRow = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

Here is the complete code:

Sub FloorPlanRequestsFormulasRange()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim myWorksheet As Worksheet
 
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long
 
    Dim myNamedRangeDynamic As Range
 
    Dim myRangeName As String
 
    Set myWorksheet = ThisWorkbook.Worksheets("FloorPlanRequests")
 
    myFirstRow = 1
    myFirstColumn = 5
    myLastColumn = 8
 
    myRangeName = "FloorPlanRequestsFormulas"
 
    With myWorksheet.Cells
 
        myLastRow = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        myLastColumn = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
 
        Set myNamedRangeDynamic = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
 
End With
 
    ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRangeDynamic
    
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
 
End Sub

 

[Fluff]

You have just put it back as it was before & not changed like I showed. It should be

        Set Fnd = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        If Fnd Is Nothing Then
            MsgBox "no data"
            Exit Sub
         End If
        myLastRow = Fnd.Row

        Set Fnd = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        If Fnd Is Nothing Then
            MsgBox "no data"
            Exit Sub
         End If
        myLastColumn = Fnd.Column

 

[jmpatrick]

You have just put it back as it was before & not changed like I showed. It should be

        Set Fnd = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        If Fnd Is Nothing Then
            MsgBox "no data"
            Exit Sub
         End If
        myLastRow = Fnd.Row

        Set Fnd = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        If Fnd Is Nothing Then
            MsgBox "no data"
            Exit Sub
         End If
        myLastColumn = Fnd.Column

We're getting closer! Getting this error:

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

at this point:

ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRangeDynamic

Here's the current full code:

Sub FloorPlanRequestsFormulasRange()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim myWorksheet As Worksheet
 
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long
    Dim Fnd As Range
   
    Dim myNamedRangeDynamic As Range
 
    Dim myRangeName As String
 
    Set myWorksheet = ThisWorkbook.Worksheets("FloorPlanRequests")
 
    myFirstRow = 1
    myFirstColumn = 5
    myLastColumn = 8
 
    myRangeName = "FloorPlanRequestsFormulas"
 
    With myWorksheet.Cells
 
        Set Fnd = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        If Fnd Is Nothing Then
            MsgBox "no data"
            Exit Sub
         End If
        myLastRow = Fnd.Row

        Set Fnd = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        If Fnd Is Nothing Then
            MsgBox "no data"
            Exit Sub
         End If
        myLastColumn = Fnd.Column
 
End With
 
    ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRangeDynamic
  
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
 
End Sub

 

[Fluff]

You haven't assigned anything to myNamedRangeDynamic

 

[jmpatrick]

You haven't assigned anything to myNamedRangeDynamic

Yes! You are correct. I had inadvertently deleted a line. Here's the code that works:

Sub FloorPlanRequestsFormulasRange()

    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Dim myWorksheet As Worksheet
 
    Dim myFirstRow As Long
    Dim myLastRow As Long
    Dim myFirstColumn As Long
    Dim myLastColumn As Long
    Dim Fnd As Range
   
    Dim myNamedRangeDynamic As Range
 
    Dim myRangeName As String
 
    Set myWorksheet = ThisWorkbook.Worksheets("FloorPlanRequests")
 
    myFirstRow = 1
    myFirstColumn = 5
    myLastColumn = 8
 
    myRangeName = "FloorPlanRequestsFormulas"
 
    With myWorksheet.Cells
 
        Set Fnd = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        If Fnd Is Nothing Then
            'MsgBox "no data"
            Exit Sub
         End If
        myLastRow = Fnd.Row

        Set Fnd = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
        If Fnd Is Nothing Then
            'MsgBox "no data"
            Exit Sub
         End If
        myLastColumn = Fnd.Column
       
        Set myNamedRangeDynamic = .Range(.Cells(myFirstRow, myFirstColumn), .Cells(myLastRow, myLastColumn))
 
End With
 
    ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRangeDynamic
  
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
 
End Sub

Thanks!

 

[Fluff]

Glad it's sorted & thanks for the feedback.