multiple user forms

[RAJESH1960]

Hello guys,
I have a code which runs for around more or less 10 minutes to get the result depending on the data. I want to use user form to display a progress bar Extracting.. and show the percentage in the bar as 10, 20, ,30...... With one user form it is not possible I hope. So, is it possible to create multiple user forms and enter user form1.show Do Events, user form2..show Do Events, etc., at different lines in the code and get the display till 100 %. ?

 

[johnnyL]

Why is your code taking 10 minutes to run?

Anyways, you want to have a look here

 

[RAJESH1960]

Thanks for the link. I will try it out today.

Anyways, you want to have a look here

 

[RAJESH1960]

Why is your code taking 10 minutes to run?

This if for only 250 rows. Just imagine how much time to extract 9000 or more rows. I have limited the extraction to 1000 rows at a time which will take at least 1/2 hour. This is because I have created this application without your help.

 

[johnnyL]

... This is because I have created this application without your help.

If you know which subroutine is taking the most time, post the code for that subroutine so we can take a look at it to see why it is taking so long to complete.

 

[RAJESH1960]

I have created 12+2 modules in the application and all are connected to each other. The problem is when I run the code with 100 rows it takes a minute to extract data but if I replace the data in B2B sheet with 1000 rows it takes an hour to extract data. In both the cases the result is correct. I am not sure which code is slowing down the extract when I try to extract 1000 rows. In some cases, there are data which have 9000 -13000 rows to be extracted.

 

[johnnyL]

Ok. Well post the full workbook & I will see where the bottleneck(s) is/are.

 

[RAJESH1960]

Please do not comment on the presentation. I am an apprentice and I have tried to create something which will work perfectly. I have written next to each macro the time taken to run for 100 rows.
code runs for 57 minutes.xlsm
Originally, there is only one code button in the code button sheet. But, for your reference I have created all the buttons to display which button takes how much time to run.

 

[johnnyL]

Ok. your workbook mentions 'GetMasterdata' taking 50-60 seconds. Upon looking at that subroutine, it does do a lot of reads/writes to sheets which will definitely slow it down.

Try replacing that subroutine with the following code:

Sub GetMasterData()
'
    Application.ScreenUpdating = False                                                                  ' Turn ScreenUpdating off
    Application.DisplayAlerts = False                                                                   ' Turn DisplayAlerts off
'
    Dim FoundStateMatch         As Boolean
    Dim ArrayRow                As Long
    Dim DestinationLastRow      As Long
    Dim StateCodesArrayRow      As Long
    Dim StateCodeToFind         As String
    Dim StateCodesArray         As Variant
    Dim VerifiedGSTIN_Array     As Variant
    Dim desWS                   As Worksheet
    Dim srcWS                   As Worksheet
'
    Set srcWS = Sheets("GSTIN Verified")
    Set desWS = Sheets("MasterData")
'
    DestinationLastRow = desWS.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row                ' Find last used row in the destination sheet
'
    StateCodesArray = Sheets("State codes").Range("A1:B" & Sheets("State codes").Range("A" & _
            Sheets("State codes").Rows.Count).End(xlUp).Row)                                            ' Save Columns A:B from 'State Codes' into 2D 1 Based StateCodesArray
'
    VerifiedGSTIN_Array = srcWS.Range("D2:T" & srcWS.Range("D" & srcWS.Rows.Count).End(xlUp).Row)       ' Save Columns D:T from 'GSTIN Verified' into 2D 1 Based VerifiedGSTIN_Array
'
    ReDim DestinationArray(1 To UBound(VerifiedGSTIN_Array, 1), 1 To 10) As Variant                     ' Set the Row & Column size for DestinationArray
'
    For ArrayRow = 1 To UBound(VerifiedGSTIN_Array, 1)                                                  ' Loop through rows of VerifiedGSTIN_Array
        DestinationArray(ArrayRow, 1) = VerifiedGSTIN_Array(ArrayRow, 3)                                '   Save Column F value from 'GSTIN Verified' to Column B of 'MasterData'
        DestinationArray(ArrayRow, 2) = "Sundry Creditors"                                              '   Save 'Sundry Creditors' to Column C of 'MasterData'
        DestinationArray(ArrayRow, 3) = VerifiedGSTIN_Array(ArrayRow, 1)                                '   Save Column D value from 'GSTIN Verified' to Column D of 'MasterData'
'
        If DestinationArray(ArrayRow, 3) <> "" Then                                                     '   If column D of destination sheet is blank then ...
            DestinationArray(ArrayRow, 4) = "Regular"                                                   '       Set column E of destination sheet to 'Regular'
'
            StateCodeToFind = Left$(VerifiedGSTIN_Array(ArrayRow, 1), 2)                                '       Set StateCodeToFind to the first two characters of column D of 'GSTIN Verified'
'
            FoundStateMatch = False                                                                     '       Set FoundStateMatch flag to 'False'
'
            For StateCodesArrayRow = 1 To UBound(StateCodesArray, 1)                                    '       Loop through rows of StateCodesArray
                If StateCodesArray(StateCodesArrayRow, 1) = StateCodeToFind Then                        '           If column A of 'State codes' = StateCodeToFind then ...
                    DestinationArray(ArrayRow, 5) = StateCodesArray(StateCodesArrayRow, 2)              '               Save column B of 'State codes' to column F of destination sheet
                    FoundStateMatch = True                                                              '               Set FoundStateMatch flag to 'True'
                    Exit For                                                                            '               Exit this For loop
                End If
            Next                                                                                        '       Loop back
'
            If FoundStateMatch = False Then MsgBox StateCodeToFind & " does not exist in State Codes."  '       If no state match was found then displays message
        Else                                                                                            '   Else ...
            DestinationArray(ArrayRow, 4) = "Unregistered"                                              '       Set column E of destination sheet to 'Unregistered'
        End If
'
        DestinationArray(ArrayRow, 6) = VerifiedGSTIN_Array(ArrayRow, 14)                               '   Copy column Q from 'GSTIN Verified' to column G of destination sheet
        DestinationArray(ArrayRow, 7) = VerifiedGSTIN_Array(ArrayRow, 15)                               '   Copy column R from 'GSTIN Verified' to column H of destination sheet
        DestinationArray(ArrayRow, 8) = VerifiedGSTIN_Array(ArrayRow, 16)                               '   Copy column S from 'GSTIN Verified' to column I of destination sheet
        DestinationArray(ArrayRow, 9) = VerifiedGSTIN_Array(ArrayRow, 17)                               '   Copy column T from 'GSTIN Verified' to column J of destination sheet
'
        DestinationArray(ArrayRow, 10) = VerifiedGSTIN_Array(ArrayRow, 6)                               '   Copy column I from 'GSTIN Verified' to column K of destination sheet
    Next                                                                                                ' Loop back
'
    desWS.Range("B" & DestinationLastRow + 1).Resize(UBound(DestinationArray, 1), _
            UBound(DestinationArray, 2)) = DestinationArray                                             ' Display the DestinationArray to the destination range
'
    Application.ScreenUpdating = True                                                                   ' Turn ScreenUpdating back on
    Application.DisplayAlerts = True                                                                    ' Turn DisplayAlerts back on
End Sub

That replacement code uses arrays to write and loop through & then writes the final result to the destination sheet. That should be waaaaaaay faster than the original code in that subroutine.

Test that & see if it works for you.

 

[RAJESH1960]

Ok. your workbook mentions 'GetMasterdata' taking 50-60 seconds. Upon looking at that subroutine, it does do a lot of reads/writes to sheets which will definitely slow it down.

Try replacing that subroutine with the following code:

Sub GetMasterData()
'
    Application.ScreenUpdating = False                                                                  ' Turn ScreenUpdating off
    Application.DisplayAlerts = False                                                                   ' Turn DisplayAlerts off
'
    Dim FoundStateMatch         As Boolean
    Dim ArrayRow                As Long
    Dim DestinationLastRow      As Long
    Dim StateCodesArrayRow      As Long
    Dim StateCodeToFind         As String
    Dim StateCodesArray         As Variant
    Dim VerifiedGSTIN_Array     As Variant
    Dim desWS                   As Worksheet
    Dim srcWS                   As Worksheet
'
    Set srcWS = Sheets("GSTIN Verified")
    Set desWS = Sheets("MasterData")
'
    DestinationLastRow = desWS.Cells.Find("*", , xlFormulas, , xlByRows, xlPrevious).Row                ' Find last used row in the destination sheet
'
    StateCodesArray = Sheets("State codes").Range("A1:B" & Sheets("State codes").Range("A" & _
            Sheets("State codes").Rows.Count).End(xlUp).Row)                                            ' Save Columns A:B from 'State Codes' into 2D 1 Based StateCodesArray
'
    VerifiedGSTIN_Array = srcWS.Range("D2:T" & srcWS.Range("D" & srcWS.Rows.Count).End(xlUp).Row)       ' Save Columns D:T from 'GSTIN Verified' into 2D 1 Based VerifiedGSTIN_Array
'
    ReDim DestinationArray(1 To UBound(VerifiedGSTIN_Array, 1), 1 To 10) As Variant                     ' Set the Row & Column size for DestinationArray
'
    For ArrayRow = 1 To UBound(VerifiedGSTIN_Array, 1)                                                  ' Loop through rows of VerifiedGSTIN_Array
        DestinationArray(ArrayRow, 1) = VerifiedGSTIN_Array(ArrayRow, 3)                                '   Save Column F value from 'GSTIN Verified' to Column B of 'MasterData'
        DestinationArray(ArrayRow, 2) = "Sundry Creditors"                                              '   Save 'Sundry Creditors' to Column C of 'MasterData'
        DestinationArray(ArrayRow, 3) = VerifiedGSTIN_Array(ArrayRow, 1)                                '   Save Column D value from 'GSTIN Verified' to Column D of 'MasterData'
'
        If DestinationArray(ArrayRow, 3) <> "" Then                                                     '   If column D of destination sheet is blank then ...
            DestinationArray(ArrayRow, 4) = "Regular"                                                   '       Set column E of destination sheet to 'Regular'
'
            StateCodeToFind = Left$(VerifiedGSTIN_Array(ArrayRow, 1), 2)                                '       Set StateCodeToFind to the first two characters of column D of 'GSTIN Verified'
'
            FoundStateMatch = False                                                                     '       Set FoundStateMatch flag to 'False'
'
            For StateCodesArrayRow = 1 To UBound(StateCodesArray, 1)                                    '       Loop through rows of StateCodesArray
                If StateCodesArray(StateCodesArrayRow, 1) = StateCodeToFind Then                        '           If column A of 'State codes' = StateCodeToFind then ...
                    DestinationArray(ArrayRow, 5) = StateCodesArray(StateCodesArrayRow, 2)              '               Save column B of 'State codes' to column F of destination sheet
                    FoundStateMatch = True                                                              '               Set FoundStateMatch flag to 'True'
                    Exit For                                                                            '               Exit this For loop
                End If
            Next                                                                                        '       Loop back
'
            If FoundStateMatch = False Then MsgBox StateCodeToFind & " does not exist in State Codes."  '       If no state match was found then displays message
        Else                                                                                            '   Else ...
            DestinationArray(ArrayRow, 4) = "Unregistered"                                              '       Set column E of destination sheet to 'Unregistered'
        End If
'
        DestinationArray(ArrayRow, 6) = VerifiedGSTIN_Array(ArrayRow, 14)                               '   Copy column Q from 'GSTIN Verified' to column G of destination sheet
        DestinationArray(ArrayRow, 7) = VerifiedGSTIN_Array(ArrayRow, 15)                               '   Copy column R from 'GSTIN Verified' to column H of destination sheet
        DestinationArray(ArrayRow, 8) = VerifiedGSTIN_Array(ArrayRow, 16)                               '   Copy column S from 'GSTIN Verified' to column I of destination sheet
        DestinationArray(ArrayRow, 9) = VerifiedGSTIN_Array(ArrayRow, 17)                               '   Copy column T from 'GSTIN Verified' to column J of destination sheet
'
        DestinationArray(ArrayRow, 10) = VerifiedGSTIN_Array(ArrayRow, 6)                               '   Copy column I from 'GSTIN Verified' to column K of destination sheet
    Next                                                                                                ' Loop back
'
    desWS.Range("B" & DestinationLastRow + 1).Resize(UBound(DestinationArray, 1), _
            UBound(DestinationArray, 2)) = DestinationArray                                             ' Display the DestinationArray to the destination range
'
    Application.ScreenUpdating = True                                                                   ' Turn ScreenUpdating back on
    Application.DisplayAlerts = True                                                                    ' Turn DisplayAlerts back on
End Sub

That replacement code uses arrays to write and loop through & then writes the final result to the destination sheet. That should be waaaaaaay faster than the original code in that subroutine.

Test that & see if it works for you.

I hope You mean replace the whole code. I will check and revert back.