Evaluating 3 Variables

[AG]

I have 3 variables named “Sellâ€, “Buy†and “Stop†that I wish to use in a
sheet named “Testâ€.

The variable Sell ranges from 1.00 to 1.99 in increments of .01 and would be
entered in cell H1 of Test.
The variables Buy and Stop range from .5 to .99 in increments of .01 and
would be entered in cells J1 and L1 respectively.

I have mutual fund closing price data in columns A and B beginning on row 4
and ending on row 7 and ending in row 1700. Various formulas are entered in
columns C through P on each row.

The result of the variables is in column N for each day on each of the rows
from 7 through 1700.
I am looking to record the last result of the last day which would be in
cell N1700 on the sheet Test.

I hope to place all possible combinations of each of the 3 variables into
the sheet Test and record the results.

I would appreciate the coding for such a task.
I use Excel 2002 SP3.
Thanks for any assistance offered.

 

[Tom Ogilvy]

sub Simulate()
Dim rw as Long, i as Long, j as Long, k as Long
dim calc as long
set calc = Application.Calculation
Application.Calculation = xlManual
rw = 2
for i = 100 to 199
Range("H1").Value = i/100
for j = 5 to 99
Range("J1").Value = j/100
for k = 5 to 99
Range("L1").Value = k/100
Application.Calculate
cells(rw,"R") = i/100
cells(rw,"S") = j/100
cells(rw,"T") = k/100
cells(rw,"U") = Range("N1700").value
rw = rw + 1
Next k
Next j
Next i
Application.Calculation = Calc
End sub

 

[AG]

Thank you Mr. Ogilvy for your assistance.
The macro works, albeit up to the point when the results sheet Test reaches
Excel’s row limitation.
I forgot the number of probabilities is 99*50*50 or 247,500 or in Excel’s
case rows!
I can try to tweak your coding to work around the issue or perhaps you have
an easy suggestion.

BTW, if you are interested I can forward the finalized workbook which is a
Buy/Sell system based on a system proffered by James B. Stewart, a columnist
for SmartMoney magazine whose articles also appear in the Wall Street Journal.

 

[AG]

I forgot to mention I had to rem-out the line

Set calc = Application.Calculation

As I got a compilation error: Object required
This didn't seem to effect the results for as far as they were able to test.

 

[Tom Ogilvy]

Yes, the set was a typo/mental glitch. I have deleted it below

sub Simulate()
Dim rw as Long, i as Long, j as Long, k as Long
dim calc as long, icol as Long
calc = Application.Calculation
Application.Calculation = xlManual
rw = 2
icol = 18
for i = 100 to 199
Range("H1").Value = i/100
for j = 5 to 99
Range("J1").Value = j/100
for k = 5 to 99
Range("L1").Value = k/100
Application.Calculate
cells(rw,icol) = i/100
cells(rw,icol + 1) = j/100
cells(rw,icol + 2) = k/100
cells(rw,icol + 3) = Range("N1700").value
rw = rw + 1
if rw > 65536 then
icol = icol + 5
rw = 2
end if
Next k
Next j
Next i
Application.Calculation = Calc
End sub

 

[AG]

I have 3 variables named “Sellâ€, “Buy†and “Stop†that I wish to use in a
sheet named “Testâ€.

The variable Sell ranges from 1.00 to 1.99 in increments of .01 and would be
entered in cell H1 of Test.
The variables Buy and Stop range from .5 to .99 in increments of .01 and
would be entered in cells J1 and L1 respectively.

I have mutual fund closing price data in columns A and B beginning on row 4
and ending on row 7 and ending in row 1700. Various formulas are entered in
columns C through P on each row.

The result of the variables is in column N for each day on each of the rows
from 7 through 1700.
I am looking to record the last result of the last day which would be in
cell N1700 on the sheet Test.

I hope to place all possible combinations of each of the 3 variables into
the sheet Test and record the results.

I would appreciate the coding for such a task.
I use Excel 2002 SP3.
Thanks for any assistance offered.

 

[AG]

I forgot to get back to you and thank you for the assistance.
With over 240k results I ran it overnight.
The revised code worked fine.
Thanks again.