OilEconomist
Active Member
- Joined
- Dec 26, 2016
- Messages
- 421
- Office Version
- 2019
- Platform
- Windows
Thanks in advance for your assistance. How can I fix the Run-Time error and get the code to execute properly and also prevent the rows from getting shifted up when I delete the old data validation.
I would like to set a name range and then create a data validation. I would like to this macro to execute:
(1) without having to activate any Worksheets
(2) check if the name range exists and delete if and make a new one (I don't think I put code in to delete if it exists)
(3) check if the data validation exists and if so delete it and make a new one
The issue becomes with the following where I get an error "Run-time error '438': Object doesn't support this property or method"
I would like to set a name range and then create a data validation. I would like to this macro to execute:
(1) without having to activate any Worksheets
(2) check if the name range exists and delete if and make a new one (I don't think I put code in to delete if it exists)
(3) check if the data validation exists and if so delete it and make a new one
The issue becomes with the following where I get an error "Run-time error '438': Object doesn't support this property or method"
VBA Code:
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="=Tickers.Nm.Rng"
VBA Code:
Option Explicit
Public Sub Get_Rng_Nm()
'Dimensioning
Dim LastRow As Long
Dim RngB As Range
Dim WS_Src As Worksheet
Dim WS_Dest As Worksheet
'Set Sheets
Set WS_Src = Sheets("Our.Summary")
Set WS_Dest = Sheets("Name.Ranges")
'Code
With Sheets("Our.Summary")
LastRow = .Cells.Find(What:="*", After:=Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set RngB = .Range("B9:B" & LastRow)
RngB.Copy Destination:=WS_Dest.Range("B9")
Range("B9").Select
End With
With Sheets("Name.Ranges")
LastRow = .Cells.Find(What:="*", After:=Cells(1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Set RngB = .Range("B9:B" & LastRow)
Range("B9").Select
End With
ThisWorkbook.Names.Add Name:="Tickers.Nm.Rng", RefersTo:=RngB
'Data Validation
With Sheets("Our.Summary")
With Range("B7")
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=Tickers.Nm.Rng"
.Range("B7").Interior.ColorIndex = 37
End With
End With
End Sub