| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating Name with variable
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. -- Thanx & regards, Asif |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating Name with variable
What is in vldName and vldFormula?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Asif" wrote in message ... 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. -- Thanx & regards, Asif |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating Name with variable
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 "Bob Phillips" wrote: What is in vldName and vldFormula? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Asif" wrote in message ... 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. -- Thanx & regards, Asif |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating Name with variable
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) "Asif" wrote in message ... 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 "Bob Phillips" wrote: What is in vldName and vldFormula? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Asif" wrote in message ... 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. -- Thanx & regards, Asif |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating Name with variable
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. -- Thanx & regards, Asif "Bob Phillips" wrote: 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) "Asif" wrote in message ... 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 "Bob Phillips" wrote: What is in vldName and vldFormula? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Asif" wrote in message ... 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. -- Thanx & regards, Asif |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating Name with variable
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" wrote in message ... 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. -- Thanx & regards, Asif "Bob Phillips" wrote: 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) "Asif" wrote in message ... 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 "Bob Phillips" wrote: What is in vldName and vldFormula? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Asif" wrote in message ... 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. -- Thanx & regards, Asif |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating Name with variable
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 -- Thanx & regards, Asif "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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. -- Thanx & regards, Asif "Bob Phillips" wrote: 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) "Asif" wrote in message ... 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 "Bob Phillips" wrote: What is in vldName and vldFormula? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Asif" wrote in message ... 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. -- Thanx & regards, Asif |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating Name with variable
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" wrote in message ... 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 -- Thanx & regards, Asif "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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. -- Thanx & regards, Asif "Bob Phillips" wrote: 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) "Asif" wrote in message ... 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 "Bob Phillips" wrote: What is in vldName and vldFormula? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Asif" wrote in message ... 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. -- Thanx & regards, Asif |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Creating Name with variable
Thank you Bob. This is working now.
-- Thanx & regards, Asif "Bob Phillips" wrote: 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" wrote in message ... 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 -- Thanx & regards, Asif "Rick Rothstein (MVP - VB)" wrote: 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" wrote in message ... 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. -- Thanx & regards, Asif "Bob Phillips" wrote: 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) "Asif" wrote in message ... 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 "Bob Phillips" wrote: What is in vldName and vldFormula? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Asif" wrote in message ... 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. -- Thanx & regards, Asif |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Creating a value from a variable result | Excel Discussion (Misc queries) | |||
| Creating a Variable Range | Excel Programming | |||
| creating a variable | New Users to Excel | |||
| Macro Creating Variable and using variable in a SQL statement | Excel Programming | |||
| Macro Creating a variable which is to be used in a SQL in the Mac | Excel Programming | |||
Linear Mode
