non r1c1 name definition

[BorisS]

I'm using 07, in case that's relevant in the below.

I am having trouble making my macro name a range at each run. Tried
recording the steps I took to get a range named as follows:

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!A:A)+1,7). The range is called
Datalist, and is later used in my filters.

Auto recording is coming up with this:

ActiveWorkbook.Names.Add Name:="Datalist", RefersToR1C1:= _
"=OFFSET(Sheet2!R1C1,0,0,COUNTA(Sheet2!C)+1,7)"

which is not producing the same thing in the name manager. Once this is run
and assigned, it is coming up with inconsistent column references for the
CountA function, and I don't know to what to attribute this.

Thanks for any clarification.

 

[Don Guillett]

try
ActiveWorkbook.Names.Add Name:="Datalist", RefersTo:= _
"=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!A:A)+1,7)"

 

[OssieMac]

What cell is active when you create the recorded macro and also when you run
it? If you change the active cell and run the recorded code (and also the
code by Don) and then select Formulas ->Name Manager and click on Datalist
and check the 'refers to' at the bottom of the dialog box then it changes the
counta reference depending on which cell was active at the time of running
the code.

Regards,

OssieMac

 

[OssieMac]

Have had another look at it. Try using this so that the range for counta
becomes absolute.
ActiveWorkbook.Names.Add Name:="Datalist", RefersToR1C1:= _
"=OFFSET(Sheet2!R1C1,0,0,COUNTA(Sheet2!C3)+1,7)"

Regards,

OssieMac

 

[BorisS]

That is what was producing the problem in the first place, but I thanks. I
did try it.

 

[BorisS]

help me understand what this does, as it seems to do the same as what I'm
trying to avoid. I cannot control from which point the macro will be run,
but let's assume that I can just start out by selecting A1. Are you telling
me there is no way to just have a macro enter exactly what I want it to
enter, which is A:A, exactly what I enter in the name manager? How can it be
that there is no literal way to transcribe that text block into code that
then puts the text block into the "refers to" part of setting up a name?

In your code, what is the counta(sheet2!c3)+1 reference, and is it dependent
on position of cell as this is called??

 

[OssieMac]

If it is column A that you want to refer to then change it to C1 which is
column A but an absolute reference. (Absolute reference is one that does not
change the reference when the formula is copied to a new location)

If you record the macro, you need to make the references absolute with the $
signs. To do this, while creating the formula select just the reference in
the formula bar and press F4 and it will put the dollar signs in for you.
When you view the macro which is recorded, absolute references are shown as
the RnCn or (Row number Column number) reference. In your case because it is
just a column, the Row reference is left out.

the VBA code is:-
ActiveWorkbook.Names.Add Name:="Datalist", RefersToR1C1:= _
"=OFFSET(Sheet2!R1C1,0,0,COUNTA(Sheet2!C1)+1,7)"

If you view the info in Name manager it will appear with $ signs as this:-
=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A)+1,7)

My apologies in my first answer for repeating what you had already said. I
am guilty of not properly reading all your message.

Hope this helps.

Regards,

OssieMac