Help in troubleshooting this piece of code..

[MuskyMan]

Greetings - I've got a piece of code that I'm sure I have something wrong with, however I get no sytax or other error messages.

In a nut shell, I'm having problems when it comes to naming a range. The size of the range changes daily based on prior day's activity. My code determines how many rows the range has with a Do While loop.

Here is where the problems starts.

Range(cells(1,1),cells(r,1).select 'Do while loop sets r value

ActiveWorkbook.names.Add Name:="PriorDay",RefersToR1C1:="=Sheet1!(Cells(1,1),cells(r,1))"

Thanks for the advice.

 

[xlbo]

I would do this a bit differently:
lRow = range("A65536&quot.end(xlup).row
ActiveWorkbook.names.Add Name:="PriorDay",RefersTo:="=Sheet1!A1:A" & lRow

However, I would suggest that you may want to look at dynamic range names which expand and contract as data is added or deleted
That way you would not have to amend your range name in code
To set up a dynamic range name, goto Insert>Name>Define
Give it a name and in the normal cell reference area, enter:
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
You can then refer to this name in code or worksheet functions although you will not be able to select it by going to the drop down names menu
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde