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

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

TommyGun

MrExcel MVP
Joined
Dec 9, 2002
Messages
4,202
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
Joined
May 28, 2002
Messages
232
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
Joined
Dec 9, 2002
Messages
4,202
Can you post some more of the code as this may help see the entire process?
 

thereuare

Board Regular
Joined
May 28, 2002
Messages
232

ADVERTISEMENT

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
Joined
Jun 17, 2003
Messages
491
Code:
If Axis = "Log" Then
Range("a3000") = 0.01 'makes cell 3001 = .01
Range("a1:a2999").FormulaR1C1 = "=OFFSET(R[1]C,0,0) * " & BoxSize
    ElseIf Axis = "Fixed" Then
       Range("a3000") = BoxSize 'makes cell 3001 = BoxSize
Range("a1:a2999").FormulaR1C1 = "=OFFSET(R[1]C,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
Joined
Mar 17, 2002
Messages
2,040

ADVERTISEMENT

Hi,

I may have messed up your referencing, so please test.

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
Joined
Feb 17, 2002
Messages
9,700
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>
[a1:a3000].Sort Key1:=[a1], Order1:=xlDescending, Header:=xlGuess
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
Joined
May 28, 2002
Messages
232
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
Joined
Feb 17, 2002
Messages
9,700
You are welcome. :biggrin:
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. :biggrin: 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. :biggrin:
 

Watch MrExcel Video

Forum statistics

Threads
1,129,809
Messages
5,638,482
Members
417,027
Latest member
wlknspc7

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