How to ADD workbook name and get result without quote?

[bojzon]

This part of code produce in DEFINE NAME>NAMES>REFERS TO: result with quote -"".
Need suggestion how to get result without guote?

CODE
ActiveWorkbook.Names.Add Name:="Data" , RefersToR1C1:= _
"Offset(" & ShtName & "!$" & FC & "$" & FR & ";0;0;CountA(" & ShtName & "!$" & FC & ":" & "$" & FC & ");0)"

Wrong – with quote
="Offset(VZA!$H$7;0;0;CountA(VZA!$H:$H);0)"

OK –BUT NEED manual remove
=Offset(VZA!$H$7;0;0;CountA(VZA!$H:$H);0)

Thanks

Bojzon

 

[SkipVought]

Hi,

the forumla needs an [equal]

ActiveWorkbook.Names.Add Name:="Data" , RefersToR1C1:= _
"[b][red]=[/red][/b]Offset(" & ShtName & "!$" & FC & "$" & FR & ";0;0;CountA(" & ShtName & "!$" & FC & ":" & "$" & FC & ");0)"

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[TonyJollans]

Hi Bojzon,

You need to put an equals sign inside the quotes ..

[blue][tt] ... "[/tt][red][tt]=[/tt][/red][tt]Offset ...[/tt][/blue]

BUT, you should not specify Refersto[red]R1C1[/red] if you are not using R1C1 notation, so altogether you want ..

[blue]ActiveWorkbook.Names.Add Name:="Data" , [highlight]RefersTo[/highlight]:= _
"[highlight]=[/highlight]Offset(" & ShtName & "!$" & FC & "$" & FR & ";0;0;CountA(" & ShtName & "!$" & FC & ":" & "$" & FC & ");0)"[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[bojzon]

I tried insert equal = but I got error : Run time error 1004
(offXP, xls2003).

CODE above - without equal (=) In DEFINE NAME>NAMES>REFERS TO: produce ="Offset..........." (like text)

 

[SkipVought]

then you have an error in your substitution values[/v], because the formula needs

RefersTo:= "[b][red]=[/red][/b]Offset....."

Step into the sub and STOP just before executing the Add statement.

RefersTo:= _
[highlight]"=Offset(" & ShtName & "!$" & FC & "$" & FR & ";0;0;CountA(" & ShtName & "!$" & FC & ":" & "$" & FC & ");0)"[/highlight]

Select the entire formula, View/Watch Window - add
and observe the VALUE of the string

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[TonyJollans]

Did you also change RefersToR1C1 to RefersTo?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[SkipVought]

ALSO!!!!!

you have asome basic problems with your formula

1. NO SEMI-COLONS -- use comas

2. neither of the last two arguments can be ZERO
[tt]
=Offset(VZA!$H$7[red];[/red]0[red];[/red]0[red];[/red]CountA(VZA!$H:$H)[red];0[/red])

[/tt]


Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[TonyJollans]

Hi Skip,

Commas, Semicolons - it depends on International Settings. If using European notation with commas for decimal points, then formula arguments must be delimited with something else (semicolons).

But spot on about the zero

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[SkipVought]

Must be my jingoistic American tunnel vision. ;-)

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}