0

The below code is writing an invoice's fields to an array. Most rows have a PO# starting with "MX" (eg. MX111111, MX222222), and it's always either in column E or F. All other data fields are the same offset relative to the PO#. For example, "item code" always one cell away, "hours" always two cells away. Hence the array populates to the value of POCol + 1, POCol + 2 etc.

I'm getting a "VBA run-time error 1004 : Application-defined or object-defined error" on line 23 of below code - when it starts trying to write cell values to the array.

I'm 99% sure it's because integer variable "POCol" is not getting a value. Why? If I set POCol = 5 outside the if statements - no errors - but it defeats the purpose of check cols E & F for the invoices it's in F.

So... I'm guessing there is an issue with my InStr if statement? The MsgBox's never pop-up, so I'm guessing my if logic never comes through.

Can anyone see any issues, and is there any more info I need to give?

Thanks - really appreciate any help.

    line = 1
For row = 1 To 100

    ' When ColA hits "Comments", the line numbers are done, so skip below ie. stop looking for more line numbers and go to next invoice
    If Cells(row, "A").Value = "Comments" Then
        Exit For
    End If
     
    ' Find which column the PO number is in
    POCol = 0
    If InStr(1, Cells(row, "E").Value, "MX") <> 0 Then
        MsgBox "Found MX ColE"
        POCol = 5
    End If
    If InStr(1, Cells(row, "F").Value, "MX") <> 0 Then
        MsgBox "Found MX ColF"
        POCol = 6
    End If
                         
    If IsNumeric(Cells(row, "A")) And Len(Cells(row, "A")) = 1 Then     ' if it's a single digit in ColA, it's a line number
                        
        If Cells(row, "A").Value = 1 And Len(Cells(row, "A")) = 1 Then   ' if first line, refer to columns one-row-down
            DataArray(invoice, 7, line, 1) = Cells(row, "A").Value           ' Line #
            DataArray(invoice, 7, line, 2) = Cells(row + 1, POCol).Value     ' PO #
            DataArray(invoice, 7, line, 3) = Cells(row + 1, POCol + 1).Value ' Item code
            DataArray(invoice, 7, line, 4) = Cells(row + 1, POCol + 2).Value ' Hours
            DataArray(invoice, 7, line, 5) = Cells(row + 1, POCol + 4).Value ' Line total
            'DataArray(invoice, 7, line, 6) = DataArray(invoice, 7, line, 5) / 10 ' Line GST
            line = line + 1                                                  ' Move to next array item
        End If
        
        If Cells(row, "A").Value > 1 And Len(Cells(row, "A")) = 1 Then    ' if other lines, refer to columns in same row
            DataArray(invoice, 7, line, 1) = Cells(row, "A").Value           ' Line #
            DataArray(invoice, 7, line, 2) = Cells(row, POCol).Value         ' PO #
            DataArray(invoice, 7, line, 3) = Cells(row, POCol + 1).Value     ' Item code
            DataArray(invoice, 7, line, 4) = Cells(row, POCol + 2).Value     ' Hours
            DataArray(invoice, 7, line, 5) = Cells(row, POCol + 4).Value     ' Line total
            'DataArray(invoice, 7, line, 6) = DataArray(invoice, 7, line, 5) / 10 ' Line GST
            line = line + 1                                                  ' Move to next array item
        End If
    
    End If
user3648620
  • 1

0 Answers0