Issue with freezing screen and userform

[zoog25]

Hello All,

I'm having an issue with a program I'm trying to code that involves using a userform to show a progress bar. I found instructions online on how to add a progress bar userform and I did my best to incorporate the code with what i was doing. The problem is that the coding i use process around 75,000 rows on an excel spreadsheet and when i run the code. Excel pops up the userform progress bar but everything is blank and i get the (Not Responding). Can someone please look at my coding and let me know where i went wrong. Thank you.

Private Sub cmdVE_Click()
    UF_CP.Hide
    UF_ProgressEtoN.Show
    MsgBox ("Done")
    UF_CP.Show
End Sub

Private Sub UserForm_Activate()
   CompEtoN
End Sub

Sub CompEtoN()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

'Declare Worksheet Variables
Dim MAINws As Worksheet, RAWws As Worksheet

'Declare Other Variables
Dim lrowMAIN As Long
Dim lrowRAW As Long
Dim drow As Long
Dim mrow As Long
Dim pcb As Long
Dim pc As Long
Dim pctCompl As Single

'Set Worksheet Variables
Set MAINws = ThisWorkbook.Worksheets("Assessments")    'Previous Assessment District Worksheet
Set RAWws = ThisWorkbook.Worksheets("Raw Data") 'New GIS Assessment District Informaiton

lrowMAIN = MAINws.Cells(Rows.Count, "C").End(xlUp).Row
lrowRAW = RAWws.Cells(Rows.Count, "H").End(xlUp).Row

UF_ProgressEtoN.Text.Caption = "0% Complete"
pcb = lrowMAIN / 100

For mrow = 2 To lrowMAIN
    drow = RawATNr(mrow)
    Select Case drow
        Case Empty
            MAINws.Cells(mrow, "Y") = "Removed"   'New/Removed Entry
    End Select
 
    Select Case mrow = pcb
        Case True
            pctCompl = (mrow - 1) / (lrowMAIN - 1)
            progressEtoN (pctCompl)
            pc = pcb + pcb
        Case False
            Select Case mrow = pc
                Case True
                    pctCompl = (mrow - 1) / (lrowMAIN - 1)
                    progressEtoN (pctCompl)
                    pc = pcb + pcb
            End Select
    End Select
                
Next mrow

UF_ProgressEtoN.Hide

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Function RawATNr(mrow As Long)

Dim MAINws As Worksheet, RAWws As Worksheet
Dim lrow As Long
Dim ATN As Variant

Set MAINws = ThisWorkbook.Worksheets("Assessments")    'Previous Assessment District Worksheet

Set RAWws = ThisWorkbook.Worksheets("Raw Data") 'New GIS Assessment District Informaiton

lrow = RAWws.Cells(Rows.Count, "H").End(xlUp).Row

With RAWws.Range("H2:H" & lrow)
    Set ATN = .Find(MAINws.Cells(mrow, "C"), LookIn:=xlValues)
        If ATN Is Nothing Then
        GoTo DoneFinding
    Else
        RawATNr = ATN.Row
    End If
End With

DoneFinding:
End Function

Sub progressEtoN(pctCompl As Single)

UF_ProgressEtoN.Text.Caption = FormatPercent(pctCompl) & " Completed"
UF_ProgressEtoN.Bar.Width = pctCompl * 204

End Sub

Sorry about the long line of coding. I'm trying to show the order in which the coding process. You have the first userform which has just several buttons. Each button activates code to do a task. For this one particular series, the main coding compares two different reports and compares is the new report removed any items from the old report. Next set of code is what i saw for accessing the progress bar for the programming, then the main code with it's own search function and then lastly the coding section for the progress bar to reflect how much the program has progressed. Thank you again for any help that you can provide.

 

[davesexcel]

Usually (Not Responding) means excel is running a very long code(loop), or possibly an infinate loop.

I'm curious to know, if you have screen updating as false, would the userform show the progress?

 

[NdNoviceHlp]

Using find is slow. See if this helps speed things up. HTH. Dave

Function RawATNr(mrow As Long)
Dim MAINws As Worksheet, RAWws As Worksheet
Dim lrow As Long
Dim ATN As Variant, BigRng As Range, Rng As Range
Set MAINws = ThisWorkbook.Worksheets("Assessments")    'Previous Assessment District Worksheet
Set RAWws = ThisWorkbook.Worksheets("Raw Data") 'New GIS Assessment District Informaiton
lrow = RAWws.Cells(Rows.Count, "H").End(xlUp).Row
Set BigRng = RAWws.Range("H2:H" & lrow)
For Each Rng In BigRng
If InStr(LCase(CStr((Rng))), LCase(CStr(MAINws.Cells(mrow, "C")))) Then
RawATNr = Rng.Row
Exit Function
End If
Next Rng
End Function

 

[NdNoviceHlp]

Finished my coffee and had a re-think. This is better. Dave

Function RawATNr(mrow As Long)
Dim MAINws As Worksheet, RAWws As Worksheet
Dim lrow As Long
Dim ATN As Variant, Arr As Variant, Cnt As Integer
Set MAINws = ThisWorkbook.Worksheets("Assessments")    'Previous Assessment District Worksheet
Set RAWws = ThisWorkbook.Worksheets("Raw Data") 'New GIS Assessment District Informaiton
lrow = RAWws.Cells(Rows.Count, "H").End(xlUp).Row
Arr = RAWws.Range("H2:H" & lrow).Value
For Cnt = LBound(Arr) To UBound(Arr)
If LCase(Arr(Cnt, 1)) = LCase(MAINws.Cells(mrow, "C")) Then
RawATNr = Cnt + 1
Exit Function
End If
Next Cnt
End Function

 

[zoog25]

Hello NdNoviceHlp,

I used your code 2nd code as you noted it and tried to do steps to see if the code works. Once i entered the function and when the the line "For Cnt = Lbound..." it gave me an overflow error. Don't know what i did wrong.

 

[zoog25]

Ok so i think i was able to research and fix the second entry code. If you Change "Cnt as Integer" to "Cnt as Long" then it works. I think it might be because in this particular list i'm going through, there are around 70,000 + entries so it might have been outside the realm of what Integer can hold. Thank you. I'm marking this as solved for now.

 

[smozgur]

I'm marking this as solved for now.

@zoog25, glad you got the solution.

About marking a post as a solution: we should mark the actual post that contains the solution code to help the future readers better, which is @NdNoviceHlp's post in this thread as you also confirmed. So I have switched the marked solution post.

Regarding the variable type which is a specific and data-based detail in this question, it surely requires the Long type since the Int type can only hold values between -32768 to +32767 and you have 70K rows.

 

[NdNoviceHlp]

You are welcome. Thanks for posting your outcome. Be safe. Dave