# Help Speed Up Code to put values in column "A"

#### thereuare

##### Board Regular
I am using a few variations of this code to put values into column A.... the variable 'BoxSize' is based upon user input:

For Count = 1 To 3000
Range("A1").Select
ActiveCell.Offset(3000, 0) = BoxSize
ActiveCell.Offset(3000 - Count, 0) = ActiveCell.Offset(3001 - Count, 0).Value + BoxSize
Next Count

I understand that i'm filling in 3000 cells, but it seems to take an excessively LONG time.

Is there a better way to accomplish this (since the variable BoxSize can change, i start inputting numbers at the bottom since i don't know the ultimate top value).

As i'm typing this i'm thinking... would it better to activate each cell and then offset by (-1,0)... or would activating each cell actually make things slower?

Thanks!

### Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

#### TommyGun

##### MrExcel MVP
Activating cells, or using the activecell object can slow down your code. Is your range always going to be 3k rows long? Also, are you simply trying to add the BoxSize variable to these 3k cells?

#### thereuare

##### Board Regular
The program is looking at stock market prices, so it'll cover a VERY wide range of prices.... whether looking at a penny stock (in which case the range would be from 0-\$5) whereas looking at the Dow Jones Index would be looking at prices of 10,000+

When the user inputs a penny stock, their box size will likely be .01 or so... when inputting a higher priced index they'll likely use a box size of 50 or 100.

The program only adds BoxSize to the previous number depending upon another variable that is selected... at other times i multiply the cell below it and other times i use pre-determined box sizes. Which to use is determined by user input and i have an If/Else statement within the code to determine which to use... the above code is just one part of the If/Else statement (i figured i'd keep it simple and if a solution was presented i could then apply it to the other sections).

Thanks again.

#### TommyGun

##### MrExcel MVP
Can you post some more of the code as this may help see the entire process?

#### thereuare

##### Board Regular

If Axis = "Log" Then
For Count = 1 To 3000
Range("A1").Select
ActiveCell.Offset(3000, 0) = 0.01 'makes cell 3001 = .01
ActiveCell.Offset(3000 - Count, 0) = ActiveCell.Offset(3001 - Count, 0).Value * BoxSize
Next Count
ElseIf Axis = "Fixed" Then
For Count = 1 To 3000
Range("A1").Select
ActiveCell.Offset(3000, 0) = BoxSize 'makes cell 3001 = BoxSize
ActiveCell.Offset(3000 - Count, 0) = ActiveCell.Offset(3001 - Count, 0).Value + BoxSize
Next Count
Else: MsgBox "There was an Error"

#### north19701

##### Active Member
Code:
``````If Axis = "Log" Then
Range("a3000") = 0.01 'makes cell 3001 = .01
Range("a1:a2999").FormulaR1C1 = "=OFFSET(RC,0,0) * " & BoxSize
ElseIf Axis = "Fixed" Then
Range("a3000") = BoxSize 'makes cell 3001 = BoxSize
Range("a1:a2999").FormulaR1C1 = "=OFFSET(RC,0,0) + " & BoxSize
Else: MsgBox "There was an Error"
End If
With Range("a1:a3000")
.Copy
.PasteSpecial Paste:=xlPasteValues
End With``````

I could be way off mark here, but would this accomplish what you needed without looping?

#### Jay Petrulis

##### MrExcel MVP

Hi,

Code:
``````Sub test()
Dim Axis As String ''''(Range???)
Dim BoxSize As Double
Dim Rng As Range

Const LastCell As Long = 3001
Const LogConst As Double = 0.01

Axis = "Log"
BoxSize = 1.1

With ActiveWorkbook
With .ActiveSheet
Set Rng = .Range(.Cells(1, 1), .Cells(LastCell - 1, 1))
If Axis = "Log" Then
.Cells(LastCell, 1) = LogConst
Rng = "=(R[+1]C*" & BoxSize & ")"
ElseIf Axis = "Fixed" Then
.Cells(LastCell, 1) = BoxSize
Rng = "=(R[+1]C+" & BoxSize & ")"
Else
MsgBox "Error!"
End If
End With
End With

End Sub``````

#### NateO

##### Legend
Autofill:

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> Filler()
<SPAN style="color:darkblue">Dim</SPAN> BoxSize <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Double</SPAN>, y <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Boolean</SPAN>
<SPAN style="color:darkblue">Let</SPAN> y = <SPAN style="color:darkblue">Not</SPAN> y
<SPAN style="color:darkblue">Let</SPAN> BoxSize = 1.1
Application.ScreenUpdating = <SPAN style="color:darkblue">False</SPAN>
[a1] = 0.01
<SPAN style="color:darkblue">If</SPAN> y <SPAN style="color:darkblue">Then</SPAN>
[a1].DataSeries Rowcol:=xlColumns, Type:=xlGrowth, Step:=BoxSize, _
Stop:=[a1] * BoxSize ^ 2999
Else: [a1].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=BoxSize, _
Stop:=[a1] + BoxSize * 2999
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
Application.ScreenUpdating = <SPAN style="color:darkblue">True</SPAN>
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN></FONT>

This is the opposite of slow. #### thereuare

##### Board Regular
NateO,

An excellent fix, but could you possibly explain the code a little bit so i can massage it for all instances i may need?

I think i understand the concept of what's happening, but in particular don't understand:

Let y = Not y (what's the purpose of this)

and...

[a1].DataSeries Rowcol:=xlColumns, Type:=xlGrowth, Step:=BoxSize, _
Stop:=[a1] * BoxSize ^ 2999
Else: [a1].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=BoxSize, _
Stop:=[a1] + BoxSize * 2999

(what is the difference between [a1] * BoxSize ^ 2999 and :=[a1] * BoxSize * 2999)

It's certainly FAST, but i'm not sure if the numbers it produces are accurate; BUT if you explain to me what's going on i can test/adjust things until i'm sure it's exactly what i need.

Anyone else that understands this code may feel free to respond.

THANKS!

#### NateO

##### Legend
You are welcome. thereuare said:
I think i understand the concept of what's happening, but in particular don't understand:

Let y = Not y (what's the purpose of this)
{snip}
This is simpy toggling the boolean, which is false by default, for the conditional test, I tested both. Substitute your conditional there.
TUA said:
[a1].DataSeries Rowcol:=xlColumns, Type:=xlGrowth, Step:=BoxSize, _
Stop:=[a1] * BoxSize ^ 2999
Else: [a1].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=BoxSize, _
Stop:=[a1] + BoxSize * 2999

(what is the difference between [a1] * BoxSize ^ 2999 and :=[a1] * BoxSize * 2999)
{snip}
Algebra. One's an exponential relationship, the other a matter of linear multiplication. If you use the latter, you'll be far too short on number generation. Try it. Maybe create a graph, this may help to illustrate the difference in the relationship. TUA said:
It's certainly FAST, but i'm not sure if the numbers it produces are accurate; BUT if you explain to me what's going on i can test/adjust things until i'm sure it's exactly what i need.
{snip}
It is pretty quick-like eh. Native functionality combined with some elementary algebra can be a beautiful thing from time to time...

I would recommend both experimenting with the functionality and approach that an auditor should take. Grab a sample of =>30 results, as you reach 30, your sample should be a valid representation of the data population. Now compare the results to your original data generated from your original procedure. Excel's an excellent tool for auditing in this manner, this can quickly be done with formulae. Actually you could compare all 3,000 values very quickly.  Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

### Forum statistics

1,167,966
Messages
5,856,560
Members
431,818
Latest member
Helpmyassignment ### 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.

### Which adblocker are you using?    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

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