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

[thereuare]

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!

 

[TommyGun]

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]

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]

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

 

[thereuare]

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]

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]

Hi,

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

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]

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]

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]

You are welcome.

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.

[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.

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.