Hello everyone,
The code below loops through multiple files in folder. I would like to write code which displays progress bar with percentage of files processed or progress of this macro in the Status Bar, whichever is easier for you. Thank you in advance for your help and suggestions.
Dilshod
Code
- Sub BuySellSignals()
- Dim wb As Workbook, ws As Worksheet
- Workbooks("MasterFile - Copy.xlsm").Worksheets("MasterSheet").Cells.Clear
- Set fso = CreateObject("Scripting.FileSystemObject")
- Set fldr = fso.GetFolder("C:\VBA\")
- Set DestSh = Workbooks("MasterFile - Copy.xlsm").Worksheets("MasterSheet")
- Application.ScreenUpdating = False
- Application.EnableEvents = False
- Application.DisplayStatusBar = False
- Application.DisplayAlerts = False
- For Each wbFile In fldr.Files
- If fso.GetExtensionName(wbFile.Name) = "csv" Then
- Set wb = Workbooks.Open(wbFile.Path)
- Worksheets(1).Activate
- Range("I1").Value = "V/SMA10"
- Columns("I:I").NumberFormat = "General"
- Range("I2").FormulaR1C1 = "=AVERAGE(RC[-1]:R[9]C[-1])"
- Range("I2").AutoFill Destination:=Range("I2:I1500")
- Range("J1").Value = "Vol/Change%"
- Columns("J:J").NumberFormat = "0.00%"
- Range("J2").FormulaR1C1 = "=(RC[-2]-RC[-1])/RC[-1]"
- Range("J2").AutoFill Destination:=Range("J2:J1500")
- Range("K1").Value = "SMA10"
- Columns("K:K").NumberFormat = "0.00$"
- Range("K2").FormulaR1C1 = "=AVERAGE(RC[-4]:R[9]C[-4])"
- Range("K2").AutoFill Destination:=Range("K2:K1500")
- Range("L1").Value = "SMA30"
- Columns("L:L").NumberFormat = "0.00$"
- Range("L2").FormulaR1C1 = "=AVERAGE(RC[-5]:R[29]C[-5])"
- Range("L2").AutoFill Destination:=Range("L2:L1500")
- Range("M1").Value = "BUY/SELL SMA10 CROSSOVER"
- Range("M2").FormulaR1C1 = "=IF(AND(RC[-2]>RC[-1],R[1]C[-2]<R[1]C[-1],RC[-1]>R[1]C[-1]),""BUY"",IF(AND(RC[-7]<RC[-4],R[1]C[-7]>R[1]C[-4]),""SELL"",""""))"
- Range("M2").AutoFill Destination:=Range("M2:M120")
- Range("O1").Value = "Close/Change%"
- Columns("O:O").NumberFormat = "0.00%"
- Range("O2").FormulaR1C1 = "=(RC[-8]-R[1]C[-8])/R[1]C[-8]"
- Range("O2").AutoFill Destination:=Range("O2:O1500")
- ActiveSheet.Columns.AutoFit
- Application.ScreenUpdating = False
- Application.EnableEvents = False
- Application.DisplayStatusBar = False
- Application.DisplayAlerts = False
- For i = 2 To 2
- If Worksheets(1).Cells(i, 13).Value = "BUY" Then
- ActiveWorkbook.Worksheets(1).Rows(i).Copy
- DestSh.Activate
- DestRowNumber = DestSh.Cells(Rows.Count, 1).End(xlUp).Row
- With DestSh.Cells(DestRowNumber + 1, 1)
- .PasteSpecial xlPasteValues
- .PasteSpecial xlPasteFormats
- Application.CutCopyMode = False
- End With
- End If
- Next
- DestSh.Columns.AutoFit
- wb.Close True
- End If
- Next wbFile
- Application.ScreenUpdating = True
- Application.EnableEvents = True
- Application.DisplayStatusBar = True
- Application.DisplayAlerts = True
- Application.CutCopyMode = False
- End Sub