0

I was looking to improve my code below to make even faster then what it currently is. I am a complete noob when it comes to Excel VBA and coding in general. I am sure their is ways to improve this code and make it run more efficiently. Specially opening and closing work books. Any help to improve this code would be greatly appreciated. My module one is Userform.show

On top of the code if anyone can help me with adding a progress bar or loading bar for the macro when its running, that would be a nice little touch.

Private Sub CommandButton1_Click()
    
    Dim RawData As String, Report As String, RawDataWorkingFolder As String, ReportWorkingFolder As String, myrange As Range, cell As Range
    
    Set aw = ActiveWorkbook
    
    Application.ScreenUpdating = False
    
    Set myrange = Worksheets("Sheet1").Range("INFO")
    BD = TextBox1.Value
    ED = TextBox2.Value
    
    a = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    RawDataWorkingFolder = Trim(aw.Worksheets(1).Range("A1").Value)
    RawData = Trim(aw.Worksheets(1).Range("E1").Value)
    Call Utilities.OpenWorkbook(RawDataWorkingFolder & RawData)
    Worksheets("DAILY").Select
    Worksheets("DAILY").Range("C6:C219,D6:D219,F6:F219").Select
    Selection.ClearContents
    Call Utilities.CloseWorkbook(RawData)
    
    Worksheets("Sheet1").Activate
    Unload Me
            
    For i = 2 To a
        If myrange.Cells(i, 1).Value >= DateValue(BD) And myrange.Cells(i, 1).Value <= DateValue(ED) Then
            If myrange.Cells(i, 5).Value = "ACH" Or myrange.Cells(i, 5).Value > 0 And myrange.Cells(i, 5).Value < 1000000 Then
                des = myrange.Cells(i, 2)
                Value = myrange.Cells(i, 4)
                ACH = myrange.Cells(i, 5)
            
                RawDataWorkingFolder = Trim(aw.Worksheets(1).Range("A1").Value)
                RawData = Trim(aw.Worksheets(1).Range("E1").Value)
                Call Utilities.OpenWorkbook(RawDataWorkingFolder & RawData)
                Worksheets("DAILY").Select
                Worksheets("DAILY").Range("D" & Rows.Count).End(xlUp).Offset(1) = des
                Worksheets("DAILY").Range("F" & Rows.Count).End(xlUp).End(xlUp).Offset(1) = Value
                Worksheets("DAILY").Range("C" & Rows.Count).End(xlUp).Offset(1) = ACH
                Call Utilities.CloseWorkbook(RawData)
                Worksheets("Sheet1").Activate
            ElseIf myrange.Cells(i, 5).Value = "CREDIT" Then
                des = myrange.Cells(i, 2)
                Value = myrange.Cells(i, 3) * -1
                ACH = myrange.Cells(i, 5)
                
                RawDataWorkingFolder = Trim(aw.Worksheets(1).Range("A1").Value)
                RawData = Trim(aw.Worksheets(1).Range("E1").Value)
                Call Utilities.OpenWorkbook(RawDataWorkingFolder & RawData)
                Worksheets("DAILY").Select
                Worksheets("DAILY").Range("D" & Rows.Count).End(xlUp).Offset(1) = des
                Worksheets("DAILY").Range("F" & Rows.Count).End(xlUp).End(xlUp).Offset(1) = Value
                Worksheets("DAILY").Range("C" & Rows.Count).End(xlUp).Offset(1) = ACH
                Worksheets("DAILY").Range("A1").Select
                Call Utilities.CloseWorkbook(RawData)
                Worksheets("Sheet1").Activate
            End If
        End If
        Application.StatusBar = "Data is Running... Percentage complete is " & Round((i / a * 100), 0) & "%"
    Next
    MsgBox ("DONE, TRANSFERRED DATA FOR" & TextBox1)
End Sub
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
Mohamad Ballout
  • 39
  • 7
  • what is the difference? If you can please inform me that way I upload it in the right section. Thank you – Mohamad Ballout Dec 20 '20 at 07:27
  • Okay no problem, I will try to post it there as well. – Mohamad Ballout Dec 20 '20 at 07:32
  • Is there an easy way to transfer the question over just out of curiosity. thanks – Mohamad Ballout Dec 20 '20 at 07:52
  • 3
    You do not need to transfer the question. This question is on topic here. Few tips for you **1.** [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) **2.** Since you are checking a range for some criteria in a loop, you may want to explore the option of `Autofilter`. A simple use of autofilter is shown [Here](https://stackoverflow.com/questions/11631363/how-to-copy-a-line-in-excel-using-a-specific-word-and-pasting-to-another-excel-s)? That will speed up things. – Siddharth Rout Dec 20 '20 at 08:04
  • 1
    **3.** You are already using `Application.StatusBar`. No need to use Progress Bar. However the `.StatusBar` may not get updated till the time you use `DoEvents` after that. If you still want to use a progress bar then you may want to see [Pop up the Excel Statusbar](https://stackoverflow.com/questions/10782394/pop-up-the-excel-statusbar) – Siddharth Rout Dec 20 '20 at 08:04
  • Also consider using `array` instead of work directly with `range`.. see [here](https://stackoverflow.com/questions/33302962/performance-difference-between-looping-range-vs-looping-array) for more information – Susilo Dec 20 '20 at 08:26

0 Answers0