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:
Here is the complete code:
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:
VBA Code:
myLastRow = .Find(What:="*", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Here is the complete code:
VBA 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