dynamic range counta error

[sammy.peters]

I am trying to make a macro that will create dynamic named ranges
since i have 500 of them to make 25 per worksheet for 24 worksheets.

i want it such that each time we hadd a row or multiple rows to the
worksheet it will update the range.
for somereason when it gets to this line it tells me that there is a
sub not defined when i try to run and it highlights the counta.

i have already tried worksheetfunctions.counta and then it moves and
gives me offset as error and on and on.

wondering if you guys know how to make it work

thanks very much


ThisWorkbook.Names.Add Name:=namerangex, _
RefersTo:=Offset("'" & nameworksheet & "!$E$6", 0, 0,
CountA("'" & nameworksheet & "!$E$E") - 1, 1), Visible:=True

 

[Dave Peterson]

If you record a macro when you do this, you'll end up with something like:

ActiveWorkbook.Names.Add Name:=namerangex, _
RefersToR1C1:="=OFFSET('" & nameworksheet & "'!R6C5,0,0," & _
"COUNTA('" & nameworksheet & "'!C5)-1,1)"

You could keep it in R1C1 reference style:

ActiveWorkbook.Names.Add Name:=namerangex, _
RefersToR1C1:="=OFFSET('" & nameworksheet & "'!R6C5,0,0," & _
"COUNTA('" & nameworksheet & "'!C5)-1,1)"

Or use A1 Reference style:

ActiveWorkbook.Names.Add Name:=namerangex, _
RefersTo:="=OFFSET('" & nameworksheet & "'!$e$6,0,0," & _
"COUNTA('" & nameworksheet & "'!$e:$e)-1,1)"


Watch your colons: $e:$e
and watch your single quotes. They surround the worksheet name.

 

[Dave Peterson]

Oops. Pasted the wrong code in the recorded sample:

ActiveWorkbook.Names.Add Name:="test1", RefersToR1C1:= _
"=OFFSET('Sheet 1'!R6C5,0,0,COUNTA('Sheet 1'!C5)-1,1)"

 

[Guest]

Dave: did you try to run your modified code? I did the same thing and it
failed. I'm still scratchiing my head figuruing out why.

Finally figured out why. the spreadsheet doesn't care that the formula
doesn't work. Meaning the values in the function are not valid.

Doing a CountA($E:$E) gets avery large number.

Sammy original code was missing the colon between the two E's.

 

[sammy.peters]

you guys are awesome.. thanks a lot.

i figured out the colon issue but i was messed up with the quotation
marks. i put it as part of the worksheet name not around it.

thanks again

 

[Dave Peterson]

I ran both versions (R1C1 and A1). And they both worked ok for me.

And =counta() will only return a very large number if there is stuff in the
range that =counta() is looking at.

Did you type the code or did you copy|paste?

If you typed, maybe you missed the closing apostrophe around the worksheet
name????

 

[sammy.peters]

ok so its created it but do i have to do something for it to
autoupdate itself like an eventhandelr?

thanks

 

[Bob Phillips]

Try this

ThisWorkbook.Names.Add Name:=namerangex, _
RefersTo:=Offset("'" & nameworksheet & "!$E$6", 0, 0,
Application.CountA("'" & nameworksheet & "!$E$E") - 1, 1), Visible:=True

--
---
HTH

Bob

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

 

[Dave Peterson]

Make sure calculation is set to automatic (I think) and then just add something
to the range (E6:E##).