| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with a crashing Macro needed
Post your code and we'll look at it...
Could be almost anything... -- steveB Remove "AYN" from email to respond "Tadragh1" wrote in message ... Dear Forumites, I am new here and just wanted to ask my first question. I am a Visual Basic newbie, so it may be naive, but I couldn't find the answer anywhere. I have a datafile of approximately 28000 rows and 6 columns. I am executing a macro on it, which is making some data analysis. It runs slowly, but it works until I add a formula to the last cell, where there is a sum of range from A1 to A30000. The summing crashes the macro. When I remove the summing from the Macro and put the formula in the cell manually, it takes forever to sum. However, when I create a new sheet within the SAME workbook and put the formula in the new sheet, it sums the column in a blink. What may cause the problem? How can I get over it? I need the sum to be in the same sheet and to be a formula, not just a value (because when I change some parameters in the sheet, I need to be able to see the sum changing in the same time). I also noticed, that creating sums from A1 to A10000, A10001 to A20000 and A20001 to A30000 and then summing those helps a bit, but it seems to be quite a stupid workaround. Thank You for Your time! -- Tadragh1 ------------------------------------------------------------------------ Tadragh1's Profile: http://www.excelforum.com/member.php...o&userid=26252 View this thread: http://www.excelforum.com/showthread...hreadid=395486 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with a crashing Macro needed
Here it goes, it is rather long, but simple: Dim koniec As Long ActiveWorkbook.SaveAs Filename:="wynEURUSD.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.ScreenUpdating = False Columns("D:G").Select Selection.NumberFormat = "0.0000" Cells.Replace What:=".", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False Range("A1") = "Quote" Range("B1") = "Date" Range("C1") = "Time" Range("D1") = "Open" Range("E1") = "High" Range("F1") = "Low" Range("G1") = "Close" Range("H1") = "TimeFrag" Range("I1") = "C" Range("J1") = "ADR C" Range("K1") = "CandleHeight" Range("L1") = "Zmienność" Range("M1") = "H-C" Range("N1") = "C-L" Range("O1") = "T?" Range("P1") = "S?" Range("Q1") = "otwpoz" Range("R1") = "adrotw" Range("S1") = "poz24h" Range("T1") = "adr24h" Range("U1") = "po24h?" Range("V1") = "adrost" Range("W1") = "stop" Range("X1") = "kier" Range("Y1") = "best" Range("Z1") = "TP" Range("AA1") = "TS" Range("AB1") = "TSclose" Range("AC1") = "przeciw" Range("AD1") = "TRIGGER:" Range("AD2") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R2C1" Range("AD3") = "SPREAD:" Range("AD4") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R2C3" Range("AD5") = "TAKE PROFIT:" Range("AD6") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Arkusz1'!R4C1" Range("AD7") = "TRAIL?" Range("AD8") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R6C1" Range("AE1") = "STOP:" Range("AE2") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R2C2" Range("AE3") = "MNOŻNIK:" Range("AE4") = "10000" Range("AE5") = "TRAILING:" Range("AE6") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R4C2" Range("AE7") = "PRZECIW?" Range("AE8") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R6C2" Columns("I").NumberFormat = "0.0000" Columns("K").NumberFormat = "0" Columns("L").NumberFormat = "0.0000" Columns("M:N").NumberFormat = "0.0000" Columns("P").NumberFormat = "0.0000" Columns("Q").NumberFormat = "0" Columns("S").NumberFormat = "0" Columns("W").NumberFormat = "0.0000" Columns("Y").NumberFormat = "0.0000" Columns("AA").NumberFormat = "0.0000" Range("A1").Select ActiveCell.End(xlDown).Select koniec = ActiveCell.Row Range("H2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=MID(RC[-5],3,4)" Range("I2") = "=RC[-2]" Range("I3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-1]=""0100"",RC[-2],"""")" Range("J2") = "=ADDRESS(ROW(RC[-3]),7,1,0)" Range("J3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-2]=""0100"",ADDRESS(ROW(RC[-3]),7,1,0),R[-1]C)" Range("K2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=R4C31*(RC5-RC6)" Range("L2") = "=AVERAGE(RC[-1]:R[30000]C[-1])" Range("M2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=ROUND(RC[-8]-INDIRECT(RC[-3],FALSE),4)" Range("N2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=ROUND(INDIRECT(RC[-4],FALSE)-RC[-8],4)" Range("O2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=IF(RC[-7]=""0100"",0,IF(R[-1]C="""","""",IF(R[-1]C=""LONG"",""LONG"",IF(R[-1]C=""SHORT"",""SHORT"",IF(R[-1]C=""LONG"","""",IF(R[-1]C[-2]=R2C30,""LONG"",IF(R[-1]C[-1]=R2C30,""SHORT"",0)))))))" Range("P2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=IF(RC[-1]=0,"""",IF(R[-1]C[-1]=""LONG"",R[-1]C,IF(R[-1]C[-1]=""SHORT"",R[-1]C,IF(RC[-1]=""LONG"",INDIRECT(RC[-6],FALSE)+R2C30-R2C31+R4C30,IF(RC[-1]=""SHORT"",INDIRECT(RC[-6],FALSE)-R2C30+R2C31-R4C30,777)))))" Range("Q2") = "0" Range("Q3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-2]<0,IF(R[-1]C[-2]=0,R[-1]C+1,R[-1]C),R[-1]C)+IF(RC[7]=""ZAMK"",-1,0)" Range("R3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R[-1]C[7]<"""",IF(RC[7]="""","""",R[-1]C),IF(RC[7]="""","""",IF(RC[7]=""LONG"",ADDRESS(ROW(RC[-15]),5,1,0),IF(RC[7]=""SHORT"",ADDRESS(ROW(RC[-15]),6,1,0),""""))))" Range("S3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-1]="""","""",IF(R[-1]C[-1]<"""",R[-1]C,IF(R[1439]C1<"""",MATCH(RC[-16],R[1]C[-16]:R[6000]C[-16],0),MATCH(""235900"",R[1]C[-16]:R[6000]C[-16],0))))" Range("T3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-1]="""","""",IF(R[-1]C[-1]<"""",R[-1]C,ADDRESS(ROW(RC[-1])+RC[-1],IF(RC[5]=""LONG"",5,6),1,0)))" Range("U3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(ADDRESS(ROW(RC[-16]),5,1,0)=RC[-1],""CZAS!"",IF(ADDRESS(ROW(RC[-16]),6,1,0)=RC[-1],""CZAS!"",""""))" Range("V3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[2]<""ZAMK"","""",IF(RC[3]=""LONG"",ADDRESS(ROW(R[-1]C[-17]),5,1,0),IF(RC[3]=""SHORT"",ADDRESS(ROW(R[-1]C[-17]),6,1,0),"""")))" Range("W3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[6]=""PRZ"",""ZAMK"",IF(RC[-2]=""CZAS!"",""ZAMK"",IF(RC[3]=""TP"",""ZAMK"",IF(RC[5]=""TCL"",""ZAMK"",IF(R[-1]C=""ZAMK"","""",IF(R[-1]C<"""",IF(RC[1]=""LONG"",IF(R[-1]C=RC[-17],""ZAMK"",R[-1]C),IF(R[-1]C<=RC[-18],""ZAMK"",R[-1]C)),IF(RC[-8]<0,IF(R[-1]C[-8]=0,RC[-7],""""),"""")))))))" Range("X3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R[-1]C[-1]=""ZAMK"","""",IF(R[-1]C<"""",R[-1]C,IF(RC[-9]<0,IF(R[-1]C[-9]=0,RC[-9],""""),"""")))" Range("Y3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-1]="""","""",IF(R[-1]C[-1]="""",IF(RC[-1]=""SHORT"",RC[-19],RC[-20]),IF(RC[-1]=""SHORT"",MIN(R[-1]C,RC[-19]),MAX(R[-1]C,RC[-20]))))" Range("Z3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R8C30=1,(IF(R[-1]C[-3]="""","""",IF(R[-1]C[-3]=""ZAMK"","""",IF(R[-1]C[-2]=""LONG"",IF(RC[-21]=R[-1]C[-3]+R6C30+R2C31,""TS"",""-""),IF(RC[-20]<=R[-1]C[-3]-R6C30-R2C31,""TS"",""-""))))),(IF(R[-1]C[-3]="""","""",IF(R[-1]C[-3]=""ZAMK"","""",IF(R[-1]C[-2]=""LONG"",IF(RC[-21]=R[-1]C[-3]+R6C30+R2C31,""TP"",""-""),IF(RC[-20]<=R[-1]C[-3]-R6C30-R2C31,""TP"",""-""))))))" Range("AA3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R8C30=0,"""",IF(R[-1]C[-3]="""","""",IF(R[-1]C[-4]=""ZAMK"","""",IF(R[-1]C[-3]=""LONG"",IF(R[-1]C<"""",MAX(R[-1]C,RC[-22]-R6C31),IF(RC[-1]=""TS"",RC[-22]-R6C31,"""")),IF(R[-1]C<"""",MIN(R[-1]C,RC[-21]+R6C31),IF(RC[-1]=""TS"",RC[-21]+R6C31,""""))))))" Range("AB3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R8C30=0,"""",IF(R[-1]C[-4]="""","""",IF(R[-1]C[-5]=""ZAMK"","""",IF(RC[-1]="""","""",IF(R[-1]C[-4]=""LONG"",IF(RC[-1]=RC[-22],""TCL"",""""),IF(RC[-1]<=RC[-23],""TCL"",""""))))))" Range("AC3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R8C31<1,"""",IF(RC[-5]="""","""",IF(RC[-5]=""LONG"",IF(RC[-14]=""SHORT"",""PRZ"",""""),IF(RC[-14]=""LONG"",""PRZ"",""""))))" Range("AG1") = "wyn na poz" Range("AG2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=IF(RC[-10]<""ZAMK"","""",ROUND(R4C31*(IF(RC[-9]=""LONG"",IF(RC[-5]=""TCL"",RC[-6]-R[-1]C[-10]-R2C31,IF(RC[-7]=""TP"",R6C30,IF(RC[-4]=""PRZ"",MAX(RC[-27]-R[-1]C[-10]-R2C31,-R2C31),-R2C31))),IF(RC[-5]=""TCL"",R[-1]C[-10]-R2C31-RC[-6],IF(RC[-7]=""TP"",R6C30,IF(RC[-4]=""PRZ"",MAX(R[-1]C[-10]-R2C31-RC[-28],-R2C31),-R2C31))))),0))" Range("AF1") = "Total:" Range("AF2") = "=SUM(RC[1]:R[30000]C[1])" ActiveWorkbook.Save -- Tadragh1 ------------------------------------------------------------------------ Tadragh1's Profile: http://www.excelforum.com/member.php...o&userid=26252 View this thread: http://www.excelforum.com/showthread...hreadid=395486 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Help with a crashing Macro needed
Didn't find too much to cause problems - but streamlined the code to avoid
selections. And defined your variable. Found word wrap on your posted copy so I added line continuation " _" to make sure... Consolidated your number format a bit. You had too many instances of selection different columns and setting them to the same format. (You can change it to anything you want) The one major issue was the value of koniec. (this might be what caused the crash; and it would have been the reason for circular reference). It could go all the way to the end of the worksheet and become 65536 (which is way beyound 30000). Redid the formula to work from the bottom up (not top down). This finds the last used cell in the column. Added an If ... Then to make sure that the minimum koniec value = 5. You may want to change that. Didn't understand why you are replacing "." with "." - but you must have a good reason. see if this works better... (worked for me in Excel 2000) Recommend you having Option Explicit at the top of all modules. This forces Excel to check all your syntax and variables. Helps find problem areas. Also - used the Watch window to monitor the value of koniec while stepping through your code (F8) and was able to see how large it became). Let us know how it works... ================================================ Dim koniec As Long ActiveWorkbook.SaveAs Filename:="wynEURUSD.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.ScreenUpdating = False ' Consolidate number formating Columns("D:AA").NumberFormat = "0.0000" Columns("K").NumberFormat = "0" Columns("Q").NumberFormat = "0" Columns("S").NumberFormat = "0" ' don't understand this - replace x with x ? Cells.Replace What:=".", Replacement:=".", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Range("A1") = "Quote" Range("B1") = "Date" Range("C1") = "Time" Range("D1") = "Open" Range("E1") = "High" Range("F1") = "Low" Range("G1") = "Close" Range("H1") = "TimeFrag" Range("I1") = "C" Range("J1") = "ADR C" Range("K1") = "CandleHeight" Range("L1") = "Zmienność" Range("M1") = "H-C" Range("N1") = "C-L" Range("O1") = "T?" Range("P1") = "S?" Range("Q1") = "otwpoz" Range("R1") = "adrotw" Range("S1") = "poz24h" Range("T1") = "adr24h" Range("U1") = "po24h?" Range("V1") = "adrost" Range("W1") = "stop" Range("X1") = "kier" Range("Y1") = "best" Range("Z1") = "TP" Range("AA1") = "TS" Range("AB1") = "TSclose" Range("AC1") = "przeciw" Range("AD1") = "TRIGGER:" Range("AD2") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R2C1" Range("AD3") = "SPREAD:" Range("AD4") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R2C3" Range("AD5") = "TAKE PROFIT:" Range("AD6") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Arkusz1'!R4C1" Range("AD7") = "TRAIL?" Range("AD8") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R6C1" Range("AE1") = "STOP:" Range("AE2") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R2C2" Range("AE3") = "MNOŻNIK:" Range("AE4") = "10000" Range("AE5") = "TRAILING:" Range("AE6") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R4C2" Range("AE7") = "PRZECIW?" Range("AE8") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R6C2" ' changed this 'Range("A1").Select 'ActiveCell.End(xlDown).Select 'koniec = ActiveCell.Row koniec = Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row If koniec < 5 Then koniec = 5 End If Range("H2").Resize(koniec - 1, 1).FormulaR1C1 = "=MID(RC[-5],3,4)" Range("I2") = "=RC[-2]" Range("I3").Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-1]=""0100"",RC[-2],"""")" Range("J2") = "=ADDRESS(ROW(RC[-3]),7,1,0)" Range("J3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(RC[-2]=""0100"",ADDRESS(ROW(RC[-3]),7,1,0),R[-1]C)" Range("K2").Resize(koniec - 1, 1).FormulaR1C1 = "=R4C31*(RC5-RC6)" Range("L2") = "=AVERAGE(RC[-1]:R[30000]C[-1])" Range("M2").Resize(koniec - 1, 1).FormulaR1C1 = _ "=ROUND(RC[-8]-INDIRECT(RC[-3],FALSE),4)" Range("N2").Resize(koniec - 1, 1).FormulaR1C1 = _ "=ROUND(INDIRECT(RC[-4],FALSE)-RC[-8],4)" Range("O2").Resize(koniec - 1, 1).FormulaR1C1 = _ "=IF(RC[-7]=""0100"",0,IF(R[-1]C="""","""",IF(R[-1]C=""LONG"",""LONG"",IF(R[-1]C=""SHORT"",""SHORT"",IF(R[-1]C=""LONG"","""",IF(R[-1]C[-2]=R2C30,""LONG"",IF(R[-1]C[-1]=R2C30,""SHORT"",0)))))))" Range("P2").Resize(koniec - 1, 1).FormulaR1C1 = _ "=IF(RC[-1]=0,"""",IF(R[-1]C[-1]=""LONG"",R[-1]C,IF(R[-1]C[-1]=""SHORT"",R[-1]C,IF(RC[-1]=""LONG"",INDIRECT(RC[-6],FALSE)+R2C30-R2C31+R4C30,IF(RC[-1]=""SHORT"",INDIRECT(RC[-6],FALSE)-R2C30+R2C31-R4C30,777)))))" Range("Q2") = "0" Range("Q3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(RC[-2]<0,IF(R[-1]C[-2]=0,R[-1]C+1,R[-1]C),R[-1]C)+IF(RC[7]=""ZAMK"",-1,0)" Range("R3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(R[-1]C[7]<"""",IF(RC[7]="""","""",R[-1]C),IF(RC[7]="""","""",IF(RC[7]=""LONG"",ADDRESS(ROW(RC[-15]),5,1,0),IF(RC[7]=""SHORT"",ADDRESS(ROW(RC[-15]),6,1,0),""""))))" Range("S3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(RC[-1]="""","""",IF(R[-1]C[-1]<"""",R[-1]C,IF(R[1439]C1<"""",MATCH(RC[-16],R[1]C[-16]:R[6000]C[-16],0),MATCH(""235900"",R[1]C[-16]:R[6000]C[-16],0))))" Range("T3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(RC[-1]="""","""",IF(R[-1]C[-1]<"""",R[-1]C,ADDRESS(ROW(RC[-1])+RC[-1],IF(RC[5]=""LONG"",5,6),1,0)))" Range("U3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(ADDRESS(ROW(RC[-16]),5,1,0)=RC[-1],""CZAS!"",IF(ADDRESS(ROW(RC[-16]),6,1,0)=RC[-1],""CZAS!"",""""))" Range("V3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(RC[2]<""ZAMK"","""",IF(RC[3]=""LONG"",ADDRESS(ROW(R[-1]C[-17]),5,1,0),IF(RC[3]=""SHORT"",ADDRESS(ROW(R[-1]C[-17]),6,1,0),"""")))" Range("W3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(RC[6]=""PRZ"",""ZAMK"",IF(RC[-2]=""CZAS!"",""ZAMK"",IF(RC[3]=""TP"",""ZAMK"",IF(RC[5]=""TCL"",""ZAMK"",IF(R[-1]C=""ZAMK"","""",IF(R[-1]C<"""",IF(RC[1]=""LONG"",IF(R[-1]C=RC[-17],""ZAMK"",R[-1]C),IF(R[-1]C<=RC[-18],""ZAMK"",R[-1]C)),IF(RC[-8]<0,IF(R[-1]C[-8]=0,RC[-7],""""),"""")))))))" Range("X3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(R[-1]C[-1]=""ZAMK"","""",IF(R[-1]C<"""",R[-1]C,IF(RC[-9]<0,IF(R[-1]C[-9]=0,RC[-9],""""),"""")))" Range("Y3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(RC[-1]="""","""",IF(R[-1]C[-1]="""",IF(RC[-1]=""SHORT"",RC[-19],RC[-20]),IF(RC[-1]=""SHORT"",MIN(R[-1]C,RC[-19]),MAX(R[-1]C,RC[-20]))))" Range("Z3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(R8C30=1,(IF(R[-1]C[-3]="""","""",IF(R[-1]C[-3]=""ZAMK"","""",IF(R[-1]C[-2]=""LONG"",IF(RC[-21]=R[-1]C[-3]+R6C30+R2C31,""TS"",""-""),IF(RC[-20]<=R[-1]C[-3]-R6C30-R2C31,""TS"",""-""))))),(IF(R[-1]C[-3]="""","""",IF(R[-1]C[-3]=""ZAMK"","""",IF(R[-1]C[-2]=""LONG"",IF(RC[-21]=R[-1]C[-3]+R6C30+R2C31,""TP"",""-""),IF(RC[-20]<=R[-1]C[-3]-R6C30-R2C31,""TP"",""-""))))))" Range("AA3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(R8C30=0,"""",IF(R[-1]C[-3]="""","""",IF(R[-1]C[-4]=""ZAMK"","""",IF(R[-1]C[-3]=""LONG"",IF(R[-1]C<"""",MAX(R[-1]C,RC[-22]-R6C31),IF(RC[-1]=""TS"",RC[-22]-R6C31,"""")),IF(R[-1]C<"""",MIN(R[-1]C,RC[-21]+R6C31),IF(RC[-1]=""TS"",RC[-21]+R6C31,""""))))))" Range("AB3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(R8C30=0,"""",IF(R[-1]C[-4]="""","""",IF(R[-1]C[-5]=""ZAMK"","""",IF(RC[-1]="""","""",IF(R[-1]C[-4]=""LONG"",IF(RC[-1]=RC[-22],""TCL"",""""),IF(RC[-1]<=RC[-23],""TCL"",""""))))))" Range("AC3").Resize(koniec - 2, 1).FormulaR1C1 = _ "=IF(R8C31<1,"""",IF(RC[-5]="""","""",IF(RC[-5]=""LONG"",IF(RC[-14]=""SHORT"",""PRZ"",""""),IF(RC[-14]=""LONG"",""PRZ"",""""))))" Range("AG1") = "wyn na poz" Range("AG2").Resize(koniec - 1, 1).FormulaR1C1 = _ "=IF(RC[-10]<""ZAMK"","""",ROUND(R4C31*(IF(RC[-9]=""LONG"",IF(RC[-5]=""TCL"",RC[-6]-R[-1]C[-10]-R2C31,IF(RC[-7]=""TP"",R6C30,IF(RC[-4]=""PRZ"",MAX(RC[-27]-R[-1]C[-10]-R2C31,-R2C31),-R2C31))),IF(RC[-5]=""TCL"",R[-1]C[-10]-R2C31-RC[-6],IF(RC[-7]=""TP"",R6C30,IF(RC[-4]=""PRZ"",MAX(R[-1]C[-10]-R2C31-RC[-28],-R2C31),-R2C31))))),0))" Range("AF1") = "Total:" Range("AF2") = "=SUM(RC[1]:R[30000]C[1])" ActiveWorkbook.Save -- steveB Remove "AYN" from email to respond "Tadragh1" wrote in message ... Here it goes, it is rather long, but simple: Dim koniec As Long ActiveWorkbook.SaveAs Filename:="wynEURUSD.xls" _ , FileFormat:=xlNormal, Password:="", WriteResPassword:="", _ ReadOnlyRecommended:=False, CreateBackup:=False Application.ScreenUpdating = False Columns("D:G").Select Selection.NumberFormat = "0.0000" Cells.Replace What:=".", Replacement:=".", LookAt:=xlPart, SearchOrder _ :=xlByRows, MatchCase:=False Range("A1") = "Quote" Range("B1") = "Date" Range("C1") = "Time" Range("D1") = "Open" Range("E1") = "High" Range("F1") = "Low" Range("G1") = "Close" Range("H1") = "TimeFrag" Range("I1") = "C" Range("J1") = "ADR C" Range("K1") = "CandleHeight" Range("L1") = "Zmienność" Range("M1") = "H-C" Range("N1") = "C-L" Range("O1") = "T?" Range("P1") = "S?" Range("Q1") = "otwpoz" Range("R1") = "adrotw" Range("S1") = "poz24h" Range("T1") = "adr24h" Range("U1") = "po24h?" Range("V1") = "adrost" Range("W1") = "stop" Range("X1") = "kier" Range("Y1") = "best" Range("Z1") = "TP" Range("AA1") = "TS" Range("AB1") = "TSclose" Range("AC1") = "przeciw" Range("AD1") = "TRIGGER:" Range("AD2") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R2C1" Range("AD3") = "SPREAD:" Range("AD4") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R2C3" Range("AD5") = "TAKE PROFIT:" Range("AD6") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Arkusz1'!R4C1" Range("AD7") = "TRAIL?" Range("AD8") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R6C1" Range("AE1") = "STOP:" Range("AE2") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R2C2" Range("AE3") = "MNOŻNIK:" Range("AE4") = "10000" Range("AE5") = "TRAILING:" Range("AE6") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R4C2" Range("AE7") = "PRZECIW?" Range("AE8") = "='E:\AA-czestosc\!MASTER\[masterEURUSD.xls]Wynik'!R6C2" Columns("I").NumberFormat = "0.0000" Columns("K").NumberFormat = "0" Columns("L").NumberFormat = "0.0000" Columns("M:N").NumberFormat = "0.0000" Columns("P").NumberFormat = "0.0000" Columns("Q").NumberFormat = "0" Columns("S").NumberFormat = "0" Columns("W").NumberFormat = "0.0000" Columns("Y").NumberFormat = "0.0000" Columns("AA").NumberFormat = "0.0000" Range("A1").Select ActiveCell.End(xlDown).Select koniec = ActiveCell.Row Range("H2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=MID(RC[-5],3,4)" Range("I2") = "=RC[-2]" Range("I3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-1]=""0100"",RC[-2],"""")" Range("J2") = "=ADDRESS(ROW(RC[-3]),7,1,0)" Range("J3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-2]=""0100"",ADDRESS(ROW(RC[-3]),7,1,0),R[-1]C)" Range("K2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=R4C31*(RC5-RC6)" Range("L2") = "=AVERAGE(RC[-1]:R[30000]C[-1])" Range("M2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=ROUND(RC[-8]-INDIRECT(RC[-3],FALSE),4)" Range("N2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=ROUND(INDIRECT(RC[-4],FALSE)-RC[-8],4)" Range("O2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=IF(RC[-7]=""0100"",0,IF(R[-1]C="""","""",IF(R[-1]C=""LONG"",""LONG"",IF(R[-1]C=""SHORT"",""SHORT"",IF(R[-1]C=""LONG"","""",IF(R[-1]C[-2]=R2C30,""LONG"",IF(R[-1]C[-1]=R2C30,""SHORT"",0)))))))" Range("P2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=IF(RC[-1]=0,"""",IF(R[-1]C[-1]=""LONG"",R[-1]C,IF(R[-1]C[-1]=""SHORT"",R[-1]C,IF(RC[-1]=""LONG"",INDIRECT(RC[-6],FALSE)+R2C30-R2C31+R4C30,IF(RC[-1]=""SHORT"",INDIRECT(RC[-6],FALSE)-R2C30+R2C31-R4C30,777)))))" Range("Q2") = "0" Range("Q3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-2]<0,IF(R[-1]C[-2]=0,R[-1]C+1,R[-1]C),R[-1]C)+IF(RC[7]=""ZAMK"",-1,0)" Range("R3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R[-1]C[7]<"""",IF(RC[7]="""","""",R[-1]C),IF(RC[7]="""","""",IF(RC[7]=""LONG"",ADDRESS(ROW(RC[-15]),5,1,0),IF(RC[7]=""SHORT"",ADDRESS(ROW(RC[-15]),6,1,0),""""))))" Range("S3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-1]="""","""",IF(R[-1]C[-1]<"""",R[-1]C,IF(R[1439]C1<"""",MATCH(RC[-16],R[1]C[-16]:R[6000]C[-16],0),MATCH(""235900"",R[1]C[-16]:R[6000]C[-16],0))))" Range("T3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-1]="""","""",IF(R[-1]C[-1]<"""",R[-1]C,ADDRESS(ROW(RC[-1])+RC[-1],IF(RC[5]=""LONG"",5,6),1,0)))" Range("U3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(ADDRESS(ROW(RC[-16]),5,1,0)=RC[-1],""CZAS!"",IF(ADDRESS(ROW(RC[-16]),6,1,0)=RC[-1],""CZAS!"",""""))" Range("V3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[2]<""ZAMK"","""",IF(RC[3]=""LONG"",ADDRESS(ROW(R[-1]C[-17]),5,1,0),IF(RC[3]=""SHORT"",ADDRESS(ROW(R[-1]C[-17]),6,1,0),"""")))" Range("W3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[6]=""PRZ"",""ZAMK"",IF(RC[-2]=""CZAS!"",""ZAMK"",IF(RC[3]=""TP"",""ZAMK"",IF(RC[5]=""TCL"",""ZAMK"",IF(R[-1]C=""ZAMK"","""",IF(R[-1]C<"""",IF(RC[1]=""LONG"",IF(R[-1]C=RC[-17],""ZAMK"",R[-1]C),IF(R[-1]C<=RC[-18],""ZAMK"",R[-1]C)),IF(RC[-8]<0,IF(R[-1]C[-8]=0,RC[-7],""""),"""")))))))" Range("X3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R[-1]C[-1]=""ZAMK"","""",IF(R[-1]C<"""",R[-1]C,IF(RC[-9]<0,IF(R[-1]C[-9]=0,RC[-9],""""),"""")))" Range("Y3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(RC[-1]="""","""",IF(R[-1]C[-1]="""",IF(RC[-1]=""SHORT"",RC[-19],RC[-20]),IF(RC[-1]=""SHORT"",MIN(R[-1]C,RC[-19]),MAX(R[-1]C,RC[-20]))))" Range("Z3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R8C30=1,(IF(R[-1]C[-3]="""","""",IF(R[-1]C[-3]=""ZAMK"","""",IF(R[-1]C[-2]=""LONG"",IF(RC[-21]=R[-1]C[-3]+R6C30+R2C31,""TS"",""-""),IF(RC[-20]<=R[-1]C[-3]-R6C30-R2C31,""TS"",""-""))))),(IF(R[-1]C[-3]="""","""",IF(R[-1]C[-3]=""ZAMK"","""",IF(R[-1]C[-2]=""LONG"",IF(RC[-21]=R[-1]C[-3]+R6C30+R2C31,""TP"",""-""),IF(RC[-20]<=R[-1]C[-3]-R6C30-R2C31,""TP"",""-""))))))" Range("AA3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R8C30=0,"""",IF(R[-1]C[-3]="""","""",IF(R[-1]C[-4]=""ZAMK"","""",IF(R[-1]C[-3]=""LONG"",IF(R[-1]C<"""",MAX(R[-1]C,RC[-22]-R6C31),IF(RC[-1]=""TS"",RC[-22]-R6C31,"""")),IF(R[-1]C<"""",MIN(R[-1]C,RC[-21]+R6C31),IF(RC[-1]=""TS"",RC[-21]+R6C31,""""))))))" Range("AB3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R8C30=0,"""",IF(R[-1]C[-4]="""","""",IF(R[-1]C[-5]=""ZAMK"","""",IF(RC[-1]="""","""",IF(R[-1]C[-4]=""LONG"",IF(RC[-1]=RC[-22],""TCL"",""""),IF(RC[-1]<=RC[-23],""TCL"",""""))))))" Range("AC3").Select Selection.Resize(koniec - 2, 1).FormulaR1C1 = "=IF(R8C31<1,"""",IF(RC[-5]="""","""",IF(RC[-5]=""LONG"",IF(RC[-14]=""SHORT"",""PRZ"",""""),IF(RC[-14]=""LONG"",""PRZ"",""""))))" Range("AG1") = "wyn na poz" Range("AG2").Select Selection.Resize(koniec - 1, 1).FormulaR1C1 = "=IF(RC[-10]<""ZAMK"","""",ROUND(R4C31*(IF(RC[-9]=""LONG"",IF(RC[-5]=""TCL"",RC[-6]-R[-1]C[-10]-R2C31,IF(RC[-7]=""TP"",R6C30,IF(RC[-4]=""PRZ"",MAX(RC[-27]-R[-1]C[-10]-R2C31,-R2C31),-R2C31))),IF(RC[-5]=""TCL"",R[-1]C[-10]-R2C31-RC[-6],IF(RC[-7]=""TP"",R6C30,IF(RC[-4]=""PRZ"",MAX(R[-1]C[-10]-R2C31-RC[-28],-R2C31),-R2C31))))),0))" Range("AF1") = "Total:" Range("AF2") = "=SUM(RC[1]:R[30000]C[1])" ActiveWorkbook.Save -- Tadragh1 ------------------------------------------------------------------------ Tadragh1's Profile: http://www.excelforum.com/member.php...o&userid=26252 View this thread: http://www.excelforum.com/showthread...hreadid=395486 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
| Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
| Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
| Macro crashing on different computer | Excel Programming | |||
| Macro Crashing Excel | Excel Programming | |||
Hybrid Mode
