Error '1004'

[kittronald]

Why does setting the RefersTo value cause a run-time error 1004 ?

Sub Macro_Change_Function()
'
' Macro_Change_Function Macro
'

'
If Sheet2.CheckBoxes("Check Box 23").Value = False Then
Sheet2.CheckBoxes("Check Box 23").Value = True
Else
Sheet2.CheckBoxes("Check Box 23").Value = True
End If

z = Sheets("Settings").Range("XLQ_Functions")
x = GetRefersTo("Fill_Formula")
a = SEARCHFORMULA(z, x)
b = ("xlqh") & Sheets("Settings").Range("Data_Type")
y = Application.Substitute(x, a, b)

ActiveWorkbook.Names("Fill_Formula").RefersTo = y


End Sub



- Ronald K.

 

[GS]

kittronald explained :

Why does setting the RefersTo value cause a run-time error 1004 ?

Sub Macro_Change_Function()
'
' Macro_Change_Function Macro
'

'
If Sheet2.CheckBoxes("Check Box 23").Value = False Then
Sheet2.CheckBoxes("Check Box 23").Value = True
Else
Sheet2.CheckBoxes("Check Box 23").Value = True
End If

z = Sheets("Settings").Range("XLQ_Functions")
x = GetRefersTo("Fill_Formula")
a = SEARCHFORMULA(z, x)
b = ("xlqh") & Sheets("Settings").Range("Data_Type")
y = Application.Substitute(x, a, b)

ActiveWorkbook.Names("Fill_Formula").RefersTo = y


End Sub



- Ronald K.

Debug.Print y to see what it is.

 

[kittronald]

Garry,

Just so everyone knows ... there's a 255 character limit for a name's
Refers to: value.

Of course, ... I already knew that. 8o


- Ronald K.

 

[GS]

Garry,

Just so everyone knows ... there's a 255 character limit for a name's
Refers to: value.

Of course, ... I already knew that. 8o


- Ronald K.

Yes, I know that! I've never tried (via code) to enter anything longer
so are you saying the length exceeded the limit and so the error?

 

[kittronald]

Garry,

That was the problem.



- Ronald K.