Find and placing elements in a long string/column of text

Question

The following is the result of downloading information from an accounting system. Basically, I was tasked with sorting through expenses from this year from an online system; once the information was downloaded from the online system, it was not formatted as a spreadsheet (so I couldn't easily use a simple lookup). The information was downloaded as a spreadsheet, however it didn't contain check numbers or names; excel formatted those away for some reason. The only thing that was left is the long stringy document, where each item in the PDF downloaded (which contained check numbers and names) was placed in column 1 (see picture 1), whereas it should have been placed in something formatted like picture 2. Obviously though PDF's do not maintain formatting.

So baring some way that I can transfer the PDF to a workbook and run an analysis (IE through copy paste or save as) I needed to get information from this long stringy thing (it's at 9000 rows at the moment, added in an excerpt).

First, this code sets the worksheet pers as a worksheet, gets the length of data in pers (example in picture 2), and length of data in expensesheet (example in picture 1)

Then it scans pers for items (prior to writing this code items were added manually - such as in the case of picture 2, 'supply 1' and corresponding information that can help denote supply 1, ie invoice #, description, date cut, and so on).

For each of those items, it then scans the "expense sheet". It tries to match the invoice number (which is the closest thing to a unique ID in this case) to the value in cell i, 1; if it exists, it then scans 'upwards' until it finds a long enough string so that it can be the 5 unit string; the one that contains a date, a check number, an amount, and a name, as well as a batch number and a memo.

Once it finds that string, it then splits it into an array, and then seeks to place it in the corresponding cells to the right of that row in worksheet pers.

Issues: 1) I keep receiving an error 400. Normally when I receive an error VBA shows what line. What is this? How can I set up an error catching block so that the editor will provide me more details on the error (ie place it occurred, reason for occurrence, etc) 2) I'm assuming that the long row (in this case its 12th from the top) can only be identified through its length. Is there a better way to identify the long row? Perhaps if it contains multiple dashes? 3) Does anyone know of a way to easily transfer a PDF of an accounting printout so that it retains its formatting when saved or copied to a spreadsheet? 4) Is there a way that this spreadsheet could be easily formatted through excel so that it can more adequately fit into the proper mold (more like picture 2)?

Option Explicit

Sub findDetailMemo()

    Dim pers As Worksheet
    Set pers = ThisWorkbook.Sheets("PERS")

    Dim persLength As Long
    persLength = pers.Range("a1").End(xlDown).Row


    Dim expenseLength As Long
    expenseLength = Range("a1").End(xlDown).Row

    Dim currentDetail() As String


    Dim i As Long
    Dim j As Long
    Dim k As Long


    Dim tempInt As Long


'first scan all of the items in the pers unit
        For k = 2 To 10
        'next scan all of the expenses
                For i = 2 To expenseLength
                    'if the invoice # is found
                    If InStr(Cells(i, 1), pers.Range("a1").Offset(k, 3)) <> 0 Then
                        'scan upwards; make sure you don't scan beyond the range of the spreadsheet
                        For j = i To 1 Step -1
                            'if the scan upwards finds a string that is 80 characters or more
                             If Len(Cells(i - j, 1)) >= 80 Then
                                'split it at the -
                                 currentDetail = Split(Cells(i - j, 1), "-", -1, vbTextCompare)
                                 'add it to the pers sheet
                                 pers.Range("a1").Offset(k, 11) = currentDetail(0)
                                 pers.Range("a1").Offset(k, 12) = currentDetail(1)
                                 pers.Range("a1").Offset(k, 13) = currentDetail(2)
                                 pers.Range("a1").Offset(k, 14) = currentDetail(3)
                                 Exit For
                            End If
                        Next j
                     Exit For
                    Else
                    End If
                Next i
        Next k
End Sub

Answer 1

EDIT: After a discussion through the chat lobby, bdpolinsky and I found what was throwing the original error 400 (which was actually error 1004).

The first issue we fixed was the InStr() and Split() functions were referencing Cell objects instead of the string within them. This was fixed by simply adding Cells().Text where strings were required.

On the line If Len(Cells(i - j, 1).Text) >= 80, we discovered that Cells() wasn't referencing the correct worksheet. The fix for this was to define Cells() as pers.Cells(), which is the worksheet the information was imported to. Happy to report that the problem bdpolinsky was having has been solved (as far as the errors go).

The following is from the original answer:

1) At the start of your code (first executable line) you can press F8 to step through the code 1 line at a time until the error is flagged.

You can also use error handlers to catch an error and have excel do something different than default. Error Handling

Sub SomeCode()
    Dim i As Integer

    On Error GoTo ErrHandler
    i = 1/0

ErrHandler:
  MsgBox "Error Description: " & Err.Description
End Sub

You can also click next to a line of code to add a Break. Breaks look like red circles, and color that line of code red. Your code will stop when it reaches this line.

2)If Len(cellThatYoureChecking) > 20 Then Code

Or

If InStr(cellThatYoureChecking, "symbolYouWantToFind") <> 0 Then Code

Or visit this post about defining how many times a character is in a string with a function. You could then make your If statement based on the number of times it occurs.

3) This part is poor form for StackOverflow, but what you're asking is a little involved so see if this tutorial is of use to you. Import table from PDF to Excel.

4) The short answer to this is yes. There are a lot of ways to reorganize data in Excel. This question is a little too broad though, and it'd be more efficient to get questions 1-3 answered first before getting too ahead of ourselves.