Named Range = strVariable resulting in "1004: Application-defined or object-defined error"

2
votes

So Strange, I have similar statements through out my VBA program, and yet this one is giving me a problem. I have verified that the named range is accurately listed. 

Dim strWarning As String
strWarning = Sheet3.Range("Dte_MissingFlag") 'Initiates to "N"

... some decision logic to flip the strWarning Flag to "Y" or "N"...

If Range("Dte_MissingFlag") <> strWarning Then 
    Range("Dte_MissingFlag") = strWarning
end if

Error occurs inside the If clause. 

1004: Application-defined or object-defined error

Debugging Completed to date: 1) While running, test in the immediate window

?strWarning
>returns "x"

?Range("Dte_MissingFlag") 
>returns null/empty

2) Run the statement Range("Dte_MissingFlag") = "x" in the immediate window with no problem.

I've read around a bit and most of these errors are due to invalid references to ranges or cells. I've tried using Sheet("name").Range("cell").value, and still get the same error.

Any thoughts on this one? Thanks in advance.

1
vbaexcel
What if you set a Range variable to the named cell at the beginning of your code? Are you maybe switching between workbooks in the omitted section? - Tim Williams
Perhaps try the code but skip over the ... some decision logic... bit to see if it still fails. - tospig
Are you using Option Explicit in the declarations section (top) of the module code page? Does Formulas ► Name Manager report Dte_MissingFlag as workbook or worksheet scope? You are using the Sheet3 codename initially but omitting it later on in the code. - user4039065

1 Answers

3
votes

I believe your named range Dte_MissingFlag is a Worksheet scope which is not in Sheet3, please check it.

I would use ThisWorkbook.Names("Dte_MissingFlag") instead.

When you use the Range("SomeName"), it usually refers to the activeworkbook. If the name is worksheet scoped, you need to use the worksheet name or ThisWorkbook.Names()