Creating Name with variable

[Asif]

Hi there,

I am trying to create Names with the following statement:

ActiveWorkbook.Names.Add Name:="vld" & sVldName, RefersTo:=vldFormula

I keep getting the runtime error 1004. I will appreciate if somebody would
help me getting round this probelm.

 

[Bob Phillips]

What is in vldName and vldFormula?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Asif]

Here are the definitions of variables:

Dim vldFormula As String, sVldName As String

sVldName = WorksheetFunction.Index(vldNames, numNames, 0)

vldFormula = "=OFFSET(Data!$B$2,MATCH(Data!$I$" & numNames + 2 & _
",Data!$B$3:$B$65536,0),3,COUNTIF(Data!$B$B,Data!$I$" &
numNames + 2 & "),1)"

--
Thanx & regards,
Asif

What is in vldName and vldFormula?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

I got an error in the formula. See if this sorts it

vldFormula = "=OFFSET(Data!$B$2,MATCH(Data!$I$" & numNames + 2 & _
",Data!$B$3:$B$65536,0),3,COUNTIF(Data!$B:$B,Data!$I$" &
numNames + 2 & "),1)"


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Here are the definitions of variables:

Dim vldFormula As String, sVldName As String

sVldName = WorksheetFunction.Index(vldNames, numNames, 0)

vldFormula = "=OFFSET(Data!$B$2,MATCH(Data!$I$" & numNames + 2 & _
",Data!$B$3:$B$65536,0),3,COUNTIF(Data!$B$B,Data!$I$" &
numNames + 2 & "),1)"

 

[Asif]

Hi Bob,

I have following statement now which is causing the same runtime error

ActiveWorkbook.Names.Item("vld" & sVldName).Delete

Above statement is before the assignment statements below.

 

[Rick Rothstein \(MVP - VB\)]

When you say it is "before the assignment statements below", do you mean it
is before this one?

sVldName = WorksheetFunction.Index(vldNames, numNames, 0)

In other words, do you mean you are trying to use sVldName **before** you
assign anything to it?

Rick

 

[Asif]

Please see below the whole block:

For numNames = 1 To NamesCount
sVldName = WorksheetFunction.Index(vldNames, numNames, 0)
ActiveWorkbook.Names.Item("vld" & sVldName).Delete
vldFormula = "=OFFSET(Data!$B$2,MATCH(Data!$I$" & numNames + 2 &
",Data!$B$3:$B$65536,0),3,COUNTIF(Data!$B:$B,Data!$I$" & numNames + 2 & "),1)"
ActiveWorkbook.Names.Add Name:="vld" & sVldName, RefersTo:=vldFormula
Next numNames

 

[Bob Phillips]

Don't bother deleting it, not necessary to re-create it.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Asif]

Thank you Bob. This is working now.