VBA and range addressing
 

When I want to address a range of cells I have been using a formula such as
the following:
Selection.AutoFill Destination:=Range.Cells("W2:W6475"), Type:=xlFillDefault
This works quite well when the length is fixed or doesn't change very much,
but I have a situation where the range varies dramatically and my thought
was to use the following :
Selection.AutoFill Destination:=Range.Cells(2,26:Limit,26),
Type:=xlFillDefault
where "limit" was the last row count and it, of course, failed.

Is there a simple way of doing this?

Thanks,
Craig

VBA and range addressing
 

How about a simpler approach

Range.Cells("W2:W6475")
Range.Cells("W2:W" & limit)
How are you defining "limit"?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"C Brandt" wrote in message
...
When I want to address a range of cells I have been using a formula such
as
the following:
Selection.AutoFill Destination:=Range.Cells("W2:W6475"),
Type:=xlFillDefault
This works quite well when the length is fixed or doesn't change very
much,
but I have a situation where the range varies dramatically and my thought
was to use the following :
Selection.AutoFill Destination:=Range.Cells(2,26:Limit,26),
Type:=xlFillDefault
where "limit" was the last row count and it, of course, failed.

Is there a simple way of doing this?

Thanks,
Craig

VBA and range addressing
 

Thanks Don:
It's amazing how simple the answer tend to be.
The reason I don't use the fixed number is that the length of the search
varies dramatically and I cannot predict where one day ends.
In answer to your question about "limit", I have a simple FOR loop where I
search for the date of the entries to change. I want the system to only
check the data for a specific date.

THANKS AGAIN,
Craig

"Don Guillett" wrote in message
...
How about a simpler approach

Range.Cells("W2:W6475")
Range.Cells("W2:W" & limit)
How are you defining "limit"?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"C Brandt" wrote in message
...
When I want to address a range of cells I have been using a formula such
as
the following:
Selection.AutoFill Destination:=Range.Cells("W2:W6475"),
Type:=xlFillDefault
This works quite well when the length is fixed or doesn't change very
much,
but I have a situation where the range varies dramatically and my
thought
was to use the following :
Selection.AutoFill Destination:=Range.Cells(2,26:Limit,26),
Type:=xlFillDefault
where "limit" was the last row count and it, of course, failed.

Is there a simple way of doing this?

Thanks,
Craig

VBA and range addressing
 

Give us an example. You may not need a loop.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"C Brandt" wrote in message
...
Thanks Don:
It's amazing how simple the answer tend to be.
The reason I don't use the fixed number is that the length of the search
varies dramatically and I cannot predict where one day ends.
In answer to your question about "limit", I have a simple FOR loop where I
search for the date of the entries to change. I want the system to only
check the data for a specific date.

THANKS AGAIN,
Craig

"Don Guillett" wrote in message
...
How about a simpler approach

Range.Cells("W2:W6475")
Range.Cells("W2:W" & limit)
How are you defining "limit"?
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"C Brandt" wrote in message
...
When I want to address a range of cells I have been using a formula
such
as
the following:
Selection.AutoFill Destination:=Range.Cells("W2:W6475"),
Type:=xlFillDefault
This works quite well when the length is fixed or doesn't change very
much,
but I have a situation where the range varies dramatically and my
thought
was to use the following :
Selection.AutoFill Destination:=Range.Cells(2,26:Limit,26),
Type:=xlFillDefault
where "limit" was the last row count and it, of course, failed.

Is there a simple way of doing this?

Thanks,
Craig