ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Help with a crashing Macro needed (https://www.excelbanter.com/excel-programming/337169-help-crashing-macro-needed.html)

Tadragh1
Help with a crashing Macro needed
 

Dear Forumites,

I am new here and just wanted to ask my first question. I am a Visua
Basic newbie, so it may be naive, but I couldn't find the answe
anywhere.

I have a datafile of approximately 28000 rows and 6 columns. I a
executing a macro on it, which is making some data analysis. It run
slowly, but it works until I add a formula to the last cell, wher
there is a sum of range from A1 to A30000. The summing crashes th
macro. When I remove the summing from the Macro and put the formula i
the cell manually, it takes forever to sum. However, when I create
new sheet within the SAME workbook and put the formula in the ne
sheet, it sums the column in a blink. What may cause the problem? Ho
can I get over it? I need the sum to be in the same sheet and to be
formula, not just a value (because when I change some parameters in th
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 A2000
and A20001 to A30000 and then summing those helps a bit, but it seem
to be quite a stupid workaround.

Thank You for Your time

--
Tadragh
-----------------------------------------------------------------------
Tadragh1's Profile: http://www.excelforum.com/member.php...fo&userid=2625
View this thread: http://www.excelforum.com/showthread.php?threadid=39548

STEVE BELL
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

Tadragh1[_2_]
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

STEVE BELL
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

Tadragh1[_3_]
Help with a crashing Macro needed
 

Dear Steve,

Thank You very very much for Your help, I really appreciate it. I am
going to check the code ASAP and will post the results.
Yes, the replace function seems weird but it works how it should, I am
using "," as the decimal place sign.


--
Tadragh1
------------------------------------------------------------------------
Tadragh1's Profile: http://www.excelforum.com/member.php...o&userid=26252
View this thread: http://www.excelforum.com/showthread...hreadid=395486

STEVE BELL
Help with a crashing Macro needed
 
You are very welcome!

Thanks for explaining the replace. It looked like you were replacing a
period with a period.
Except you meant a period with a comma.

You might be able to do this by converting the values to "local" or
something like that - converting from American to European. I don't have
any experience with this, but have seen it appear in this forum many times.

I am looking forward to hearing if your problems have been solved.

Happy Exceling...

--
steveB

Remove "AYN" from email to respond
"Tadragh1" wrote in
message ...

Dear Steve,

Thank You very very much for Your help, I really appreciate it. I am
going to check the code ASAP and will post the results.
Yes, the replace function seems weird but it works how it should, I am
using "," as the decimal place sign.


--
Tadragh1
------------------------------------------------------------------------
Tadragh1's Profile:
http://www.excelforum.com/member.php...o&userid=26252
View this thread: http://www.excelforum.com/showthread...hreadid=395486

STEVE BELL
Help with a crashing Macro needed
 
Here's a copy of one post. (except this is about going the other way)

Why my excel format set a currency as 1.000,00 not 1,000.00 ? Where should i
change the format ?



David McRitchie Jul 10, 8:34 pm show options

Newsgroups: microsoft.public.excel.misc
From: "David McRitchie" - Find messages by
this author
Date: Sun, 10 Jul 2005 23:34:02 -0400
Local: Sun, Jul 10 2005 8:34 pm
Subject: Excel format
Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

You may have set your Regional Settings to a European country
that uses that format, or you may have switched comma and period.


Windows Start, Settings, Control Panel, Regional Settings,
Check General tab that your location is correct i.e.
United States
Check Number tab that your decimal symbol is a period
Check Number tab that your digits grouping is a comma


---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm




--
steveB

Remove "AYN" from email to respond
"Tadragh1" wrote in
message ...

Dear Steve,

Thank You very very much for Your help, I really appreciate it. I am
going to check the code ASAP and will post the results.
Yes, the replace function seems weird but it works how it should, I am
using "," as the decimal place sign.


--
Tadragh1
------------------------------------------------------------------------
Tadragh1's Profile:
http://www.excelforum.com/member.php...o&userid=26252
View this thread: http://www.excelforum.com/showthread...hreadid=395486


All times are GMT +1. The time now is 12:24 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com