Can't count past 256!

JohnPoole

Active Member
Joined
Jun 9, 2005
Messages
254
Hi all, I seem to be having a problem getting the following code to place the numbers in columd D after row 256, wherby the it goesback to cell D2 and places the remaning numbers in that cell. I cant understand why, can anyone help?

Here is the code im using.

<font face=Courier New>


<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> count256()
Counter = 0
        <SPAN style="color:#00007F">Do</SPAN>
    
        Selection = Cells(Columns.Count, 4).End(xlUp).Offset(1, 0).Activate
        Selection.Value = Counter
        Counter = Counter + 1
        <SPAN style="color:#00007F">Loop</SPAN> <SPAN style="color:#00007F">Until</SPAN> Counter = 300
    
    
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

As far as i can tell this should put the numgbers 0-300 into column D.

Many thanks.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Vangaard

New Member
Joined
Oct 19, 2005
Messages
35
I may be wrong, but:
It appears that the limitation might be from using the Columns.Count

Should this instead be a Rows.Count?

Selection = Cells(Rows.Count,4).End(xlUp).Offset(1,0).Activate
 

bjwillingham

Active Member
Joined
Jun 17, 2003
Messages
287
There are 256 columns on a spreadsheet.

Your Cells(256,4).end(xlup)..... begins at row 256, so once a value is in that cell, the xlend takes it up to the top of the data.

You probably want 'rows.count'. :)
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Does this do what you want?

Code:
Sub test()
Dim r As Range
Set r = Range("D65536").End(xlUp).Offset(1)
r = 1
r.Resize(300, 1).DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Stop:=300
End Sub
 

JohnPoole

Active Member
Joined
Jun 9, 2005
Messages
254

ADVERTISEMENT

Yay! that fixed it thanks guys.
 

PolarBear

Board Regular
Joined
Sep 26, 2005
Messages
215
Just wondering, but since you didn't have a dim statement for counter, is it possible it defined itself as "byte", which has a max limit of 256? I wonder if declaring Counter as Integer might solve your problem as well.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
PolarBear

If you don't declare a variable, or don't specify the data type when you do it is a Variant.
 

Forum statistics

Threads
1,136,616
Messages
5,676,831
Members
419,653
Latest member
analyticalchemist94

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top