Problem debugging a macro....

[jay9333]

Hello,

I've written a macro that compares two excel files (fileA and fileB) row by row to look for rows that are duplicates (that are in both file). Each row in each file has a patient ID (ie. A4522335) and a visit number (ie. 3). If two rows in both files have the same patient ID and visit number, then I mark it as a duplicate. One of the files is around 7500 rows in length and the other is about 2500 rows in length.

I run the macro from fileA. It allows the user to choose the file to compare fileA to. When I choose fileB the macro starts chugging through the rows, and eventually stops responding. Sometimes it takes 10 minutes to stop, sometimes its right after I choose fileB to open. If I watch the windows task manager under the "Applications" tab another occurance of fileA appears right before excel stops responding.

I really don't know how to debug this. Usually the excel vba debugger is pretty good at telling me where the problem is, but not this time. I've tried putting a break point in the loop/compare code and stepping through, but stepping through 18.7 million loops (7500x2500) gets old quickly, or even stepping through just 7500 loops (if I break at each next row in fileA) is too much. Does anyone have any suggestions for debugging this problem?

thanks,

jay9333

 

[BrainB]

Sounds as if your code might be a bit long-winded. This code runs through a selected list of project numbers and gets other required information. Like a lookup table.

Not exactly what you want, but it gives the basic structure. If you don't want to select the range you will have to set a worksheet range. You will also need to check the visitor number of a found item.

'======================================
Sub GET_CATEGORIES()
'======================================
    Dim Project As String
    Dim ToRow As Long
    Dim ToCol As Integer
    Dim FromSheet As Worksheet
    Dim FromRow As Long
    Dim FoundCell
    Dim RowCount As Long
    '-----------------------------------
    rsp = MsgBox("You should have selected the project numbers for which categories are required." _
        & vbCr & "Will be put into 3 cells next to the Project number", _
        vbOKCancel, "CATEGORIES FOR PROJECT NUMBER LIST")
    If rsp = vbCancel Then End
    '-----------------------------------
    Application.Calculation = xlCalculationManual
    Set FromSheet = ThisWorkbook.Worksheets("Categories")
    ToRow = Selection.Cells(1, 1).Row
    ToCol = ActiveCell.Column + 1
    RowCount = Selection.Rows.Count
    '- loop
    For r = 1 To RowCount
        Application.StatusBar = r & " / " & RowCount
        Project = Selection.Cells(r, 1).Value
        Set FoundCell = FromSheet.Columns(1).Find(Project)
        If FoundCell Is Nothing Then
            ActiveSheet.Cells(ToRow, ToCol).Value = "not found"
            ActiveSheet.Cells(ToRow, ToCol + 1).Value = "?"
            'MsgBox ("Project " & UCase(Project) & " not found.")
        Else
            FromRow = FoundCell.Row
            ActiveSheet.Cells(ToRow, ToCol).Value = FromSheet.Cells(FromRow, 2).Value
            ActiveSheet.Cells(ToRow, ToCol + 1).Value = FromSheet.Cells(FromRow, 3).Value
            ActiveSheet.Cells(ToRow, ToCol + 2).Value = FromSheet.Cells(FromRow, 4).Value
        End If
        ToRow = ToRow + 1
    Next
    Application.Calculation = xlCalculationAutomatic
    Application.StatusBar = False
End Sub
'=========================================================

Regards
BrianB
Use CupOfCoffee to speed up all windows applications
================================

 

[jay9333]

Thanks Brain,

I stripped down "fileB" to 50 rows and ran the macro. As usual, excel said "stopped responding" in the task manager, but I let her run anyway. About 50 seconds later after switching between running and not responding about 7 times, wallah, the macro finished and worked as it should.

So I guess my problem was believing the task manager. At 50 seconds for 50 rows, my macro will probably take around 42 minutes for 2500 rows. So I'm just gonna let her go for an hour or so and see what happens.

Thank you so much for the code though, I'll have a look at it while I'm letting my macro run on a (faster) box... maybe I can speed things up next time. I had no idea vba was so slow. Or maybe its just my code... I'm using two nested forloops and just comparing each ptid and visitno in fileA to each one in fileB. Is that a crappy way of doing things?

blessings,

Jay9333