Incrementing the column at regular intervals

Closed
Arun - Oct 15, 2015 at 07:40 AM
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
- Oct 15, 2015 at 11:30 AM
Hello,


I need to increase the cell range at regular intervals using macros.

My code is:
Do While Cells(2, m).Value <> ""
Range("cells(2, m).value:cells(18,n).vlaue").Select
m = m + 1
n = m + 18
Loop

It is showing bug.
What is wrong rang increment process
Can some one can help in this.

1 reply

TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Oct 15, 2015 at 11:30 AM
Hi Arun,

Not exactly sure what you are trying to do, but this line doesn't make sense:
Range("cells(2, m).value:cells(18,n).vlaue").Select

Correct way would be:
Range(cells(2, m),cells(18,n)).Select

Also m = 0 until the code comes to the line m = m +1.
As long as m = 0, the range Cells(2, m) is invalid as Colum 0 doesn't exist.

If you want to select the 18 column to the right of your used range for rows 2 to 18, then a loop doesn't seem to be the most effective way.

Try something like:
Dim lCol as integer
lCol = cells(2,columns.count).end(xltoleft).column +1
range(cells(2,lcol),cells(18,lcol+18)).select

Hopefully this is helpful to you.

Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
0