VBA Error Handling Tips?

wutangzus2002

New Member
Joined
Jun 17, 2014
Messages
3
I am actually fairly new to VBA coding and is at the point where I am trying to learn how to debug a current program given to me by a coworker that I wish to understand and possibly improve to suit my needs.

Code:
Public Sub Patient_Records()
    
    Dim FF As Long, strText As String, strFile As String
    Dim i As Long, v As Variant
    Dim j As Long, arrConcat() As String, strConcat As String
    
    j = 0
    
    Const strDelimiter As String = vbLf
    
    ReDim arrConcat(1 To 1, 1 To 1)
    
    strFile = ThisWorkbook.Path & "\Tracking.txt" 'file path and name
    
    FF = FreeFile()
    Open strFile For Binary As #FF
    strText = Space$(LOF(FF))
    Get #FF, , strText
    Close #FF
    
    v = Split(strText, vbLf)
    
    For i = LBound(v) To UBound(v)
        If v(i) Like "*######-#####*" Then
            strConcat = Application.Trim(v(i))
        ElseIf v(i) Like "*COMPLETED*" Or v(i) Like "*Expires*" Then
            strConcat = strConcat & strDelimiter & Application.Trim(v(i))
            j = j + 1
            MsgBox "This is What J equals: " & j & vbLf & "This is What strConcat looks Like: " & strConcat
            ReDim Preserve arrConcat(1 To 1, 1 To j)
            arrConcat(1, j) = strConcat
            strConcat = ""
                ElseIf strConcat <> "" Then
            strConcat = strConcat & strDelimiter & Application.Trim(v(i))
        End If
    Next i
    
    Application.ScreenUpdating = False
    With Worksheets.Add(After:=Sheets(Sheets.Count))
        .Cells.WrapText = True
        .Columns("A").ColumnWidth = 100
        .Columns("B:E").ColumnWidth = 18
        With .Range("A1:E1")
            .Value = Array("Patient" & vbLf & "Information", "DATE ORDERED", _
                           "COMPLETION" & vbLf & "STATUS", _
                           "AFTER ORDER" & vbLf & "DAYS(>30 DAYS" & vbLf & "REQUIRE ACTIONS)", _
                           "PATIENT" & vbLf & "NOTIFIED", _
                           "COMMENTS")
            .HorizontalAlignment = xlCenter
            .Font.Bold = True
        End With
        .Range("A2").Resize(j - 1, 1).Value = Application.Transpose(arrConcat)
        .Columns(1).AutoFit
        .Rows.AutoFit
        
        With .Range("A1:E1").Borders
            .LineStyle = xlContinuous
            .Weight = xlMedium
            .ColorIndex = xlAutomatic
        End With
        For i = 2 To j
            With .Rows(i).Range("A1:E1").Borders
                .LineStyle = xlContinuous
                .Weight = xlMedium
                .ColorIndex = xlAutomatic
            End With
        Next i
    End With
    Application.ScreenUpdating = True
    
End Sub

This is the program I am working with when the error message appears stating that there is an application-defined or object-defined error. Run Time Error '1004'

it Points to this specific line.

Code:
.Range("A2").Resize(j - 1, 1).Value = Application.Transpose(arrConcat)

My Question is I am thinking that it is J because when highlight each individual portion I found that Highlight at the time of error j = 0. The other I wished to ask was if there was a way to change

Code:
ElseIf v(i) Like "*COMPLETED*" Or v(i) Like "*Expires*" Then
To something that can search for the paragraph instead of those keywords, the reason why is because the Raw dumb that this grabs it from tends to not always have those keywords.
Here is a random example of Dump,(Not an Actual Dump)

Code:
000000-00000 kajsdhfaksjdhfaksjfhasf
sjkdfhaskdjfhas dkfjhasf
Completed sjdkfhaksjdghaskjghasdlfkjasfl Completed


000000-00000 aklsdjfa;l skdjgas
jkasdhglaksdjf;laskjdf
Expired aksdjhgfal;sdkfjal;skdfj

My Current Set up consists of Excel 2013 on a Windows 10 OS

I would like to apologize for not shortening the Macro but I am not competent enough to show you all of the pertinent information and be able to get an accurate response in return.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

wutangzus2002

New Member
Joined
Jun 17, 2014
Messages
3
I would like to give an update. I have just spent a few hours slowly hammering away at this and was able to figure it out without too much issue, and moved on further into my project.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,172
Messages
5,623,178
Members
415,955
Latest member
Footballtend

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top