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!

 

[thereuare]

Thanks.

I understood the math sybmols (*, ^, etc), i guess after playing around with it a little this is what i can't seem to figure out:

What determines how many rows will 'fill in'? I've tried using different variables for BoxSize as well as assigning a different value to [a1] and either breaks for having too large a number or only fills in 20 rows or so.

 

[NateO]

These two procedures give me the virtually the exact same result. It differs slightly with very large numbers, the second appearing to be more accurate, I would hazard a guess that it's a floating point issue.

<font face=Courier New><SPAN style="color:darkblue">Sub</SPAN> khdsfjhskd()
<SPAN style="color:darkblue">Dim</SPAN> BoxSize <SPAN style="color:darkblue">As</SPAN> <SPAN style="color:darkblue">Double</SPAN>
BoxSize = 1.1
<SPAN style="color:darkblue">For</SPAN> Count = 1 <SPAN style="color:darkblue">To</SPAN> 3000
    Range("A1").<SPAN style="color:darkblue">Select</SPAN>
    ActiveCell.Offset(3000, 0) = 0.01 <SPAN style="color:green">'makes cell 3001 = .01</SPAN>
    ActiveCell.Offset(3000 - Count, 0) = ActiveCell.Offset(3001 - Count, 0).Value * BoxSize
<SPAN style="color:darkblue">Next</SPAN> Count

<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">Sub</SPAN>


<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 ^ 3000
Else: [a1].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=BoxSize, _
        Stop:=[a1] + BoxSize * 3000
<SPAN style="color:darkblue">End</SPAN> <SPAN style="color:darkblue">If</SPAN>
[a1:a3001].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>

The exponential product determines the stopping point, the exponent being 1 less then the row is no coincidence.

Your numbers are too large! The procedure above generates 1.50679952517329E+122 as the largest number. To be certain, not very small... Excel doesn't handle numbers > 9.99999E+307 very well.

 

[thereuare]

I really appreciate your help!

I've done some reading on DataSeries (which i've never used before) and have come up with the following two subs:

Sub Fixed()
BoxSize = 1
Application.ScreenUpdating = False 'This added after test
Set NumberRange = Range("A1:A3000")
Range("A1").Value = BoxSize
NumberRange.DataSeries Step:=BoxSize
[a1:a3000].Sort Key1:=[a1], Order1:=xlDescending, Header:=xlGuess
Application.ScreenUpdating = True
End Sub

Sub Log()
Application.ScreenUpdating = False 'This added after test
Set NumberRange = Range("A1:A3000")
Range("A1").Value = 0.01
NumberRange.DataSeries Step:=BoxSize
[a1:a3000].Sort Key1:=[a1], Order1:=xlDescending, Header:=xlGuess
Application.ScreenUpdating = True
End Sub

The first works as i need, increasing the number by the BoxSize each time.

What i'm trying to do with the second one (in red) is instead of adding the BoxSize to the previous number, i want it to MULTIPLY by the previous number. I realize this isn't valid code:
Step:=*BoxSize
but that's essentially what i'm after.

I look forward to your reply, and thank you for your patience and for teaching me something new!

 

[NateO]

Hello, you are welcome.

With all due respect, my last post shows both:

1) Multiplication
2) Addition

In fact, if you run it, because I have y set to true, it multiplies.

Note the arguments for type, being xlGrowth (multiplication) and xlLinear (addition). Also note, your stopping point needs to be calculated differently (you need to include one eh! You have omitted a few critical arguments, this is risky business), as the code also demonstrates.

When you're working with the multiplication variety, you are creating a compound growth model eh (or decay, pending your starting point). So the last point is determined via an exponential equation, which is how all compounding growth models can be summarized. I mention the graph as addition will be linear, while multiplication will be parabolic. To get y, you need to take x to an exponential factor.

Just copy the functionality from the code posted eh. Hope this helps.

 

[thereuare]

It's that i see what your code does but i don't understand each step of it... what what i wrote i understand.

It seems to me that you determine the 'loop' by working backwards.... figuring out the stop and working from there.

Just seems as if it would be much easier if i could somehow make the step do multiplication instead of addition.

I left out some arguments as VBA Help (where i read about dataseries) stated that most were optional.

If there is no easy way to have DataSeries multiply instead of add then i'll just copy/paste your code and be ignorant if/when i have to go back and tweak the program (which is why i'm hesitant to just add somethng that i don't understand)

Thanks again.

 

[NateO]

Hmmm, I'm not connecting here... Not to be a jerk, I'm starting to be curious about our starting point in this discussion.

Let me break this down, and see if it helps. Multiplication (Compounding growth):

<font face=Courier New>[a1].DataSeries Rowcol:=xlColumns, Type:=xlGrowth, Step:=BoxSize, _
        Stop:=[a1] * BoxSize ^ 3000</FONT>

Addition (linear growth):

<font face=Courier New>[a1].DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=BoxSize _
        , Stop:=[a1] + BoxSize * 3000</FONT>

Does this help? The Data Series Method wants a number to stop at, use algebra to easily pass this with a variable to assign the end-point (you can plug it to go to a certain range). There's no loop, I happen to know the final answer I want in the compounding growth model via an exponential function, it's predetermined. If you want to learn more about compounding growth, check it out:

http://www.google.com/search?q=compounding+growth+exponential

Does this help?

 

[thereuare]

OK, i'm up to speed now (at least for the purposes i need).

What i wasn't grasping was:
xlGrowth=Multiplication
xlLinear=Addition

Your code contained an exponent and i couldn't figure out what an exponent was doing in the code as i was only doing addition and multiplication... i saw that your code worked, but it didn't make sense to me. As well, the code for the addition contained multiplication further adding to the confusion.

From reading DataSeries in VBA help it stated that i didn't need a stop statement IF i declared the range... as the program would fill to the designated range. Therefore i understand the following code and it seems to work:

Sub Log()
BoxSize = 1.02
Application.ScreenUpdating = False
Set NumberRange = Range("A1:A3000")
Range("A1").Value = 0.01
NumberRange.DataSeries Type:=xlGrowth, Step:=BoxSize
[a1:a3001].Sort Key1:=[a1], Order1:=xlDescending, Header:=xlGuess
Application.ScreenUpdating = True
End Sub

Sorry it took me a bit to catch on, but as you can see i'm eventually trainable.

Thanks again!

 

[NateO]

Yes, I see. You are welcome.

I was approaching this more from a front-end implementation I suppose...

Mathematically/Non-looping processes, it works like the following: If you want to figure out a number you've added to 3000 times, you need to multiply the additional factor 3000 times, then add the result. If you want to figure out a number you've multiplied 3000 times, you need to exponentially enlarge the multiplier 3000 times, then multiply the result. I suppose I could have stated that when you first asked, to a certain extent I did...

In my opinion, if you're looking at financial markets, there's nothing wrong with having a solid understanding of compounding growth and her mathematical implications.

In any case, glad to hear you're up and running.