Object Variable/With Block Not Set When Naming Ranges

**sperry2565** · 07-09-2013, 12:19 PM

I get the "Object variable or With block not set" for the TempName.Address. This is the same code I use for 6 other variables I'm naming on 2 other sheets (this one being the 3rd) in the workbook.

Could this be because the worksheet it is pulling from has multiple "TEMP" entries in column "A"? I didn't think it would do that if I specify it to select the 2 columns next to it....am I missing something obvious?

I've attached a sample workbook that doesn't include everything just the error...
Test_EX.xlsm

**Norie** · 07-09-2013, 12:26 PM

The problem is that the code to set the range is never executed.

Try this.

Option Explicit

Sub Tst_EX()
Dim TempName As Range
Dim rngFnd As Range

    With Worksheets("TMA")
        Set rngFnd = .Cells.Find(What:="TEMP", After:=.Range("A1"), LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False)

        If Not rngFnd Is Nothing Then

            If rngFnd.Offset(0, 2).Value <> "" Then
                Set TempName = .Range(rngFnd.Offset(0, 2), rngFnd.Offset(0, 2).End(xlDown).End(xlToRight))
            End If
            TempName.Name = "TEMP"
        End If

    End With

End Sub

**sperry2565** · 07-09-2013, 12:33 PM

Is there any reason why only this named range required this when my other ones did? Do you know?

**Norie** · 07-09-2013, 12:42 PM

The error could have happened on any one of the names, depends on a few things.

For example, which sheet was active when you ran the code.

I know you are activating 'TMA' but it's always better explicitly referencing it in the code