|
Evaluating 3 Variables
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. |
Evaluating 3 Variables
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 -- Regards, Tom Ogilvy "AG" wrote: 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. |
Evaluating 3 Variables
Thank you Mr. Ogilvy for your assistance.
The macro works, albeit up to the point when the results sheet Test reaches Excels row limitation. I forgot the number of probabilities is 99*50*50 or 247,500 or in Excels 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. "Tom Ogilvy" wrote: 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 -- Regards, Tom Ogilvy "AG" wrote: 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. |
Evaluating 3 Variables
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" wrote: 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 -- Regards, Tom Ogilvy "AG" wrote: 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. |
Evaluating 3 Variables
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 -- Regards, Tom Ogilvy "AG" wrote: 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" wrote: 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 -- Regards, Tom Ogilvy "AG" wrote: 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. |
Evaluating 3 Variables
"AG" wrote: 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. |
Evaluating 3 Variables
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. |
| All times are GMT +1. The time now is 02:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com