Application-defined or object-defined error

[sudeepvns]

Hi,

Require some help here.

I have a spreadsheet with 200 columns and 819 rows, where I want the records to come out in a set format, I had help here in the past to do that, it works fine if I keep the columns to '150' instead of '201' where it reads the data into an array but if I exceed it returns "Run-time error '1004': Application-defined or object-defined error", please see the VBA code and advise:

Sub UnLineUp_v5()

  Dim a As Variant, b As Variant

  Dim i As Long, j As Long, k As Long, rowinc As Long, colinc As Long

  Dim wbCSV As Workbook

  

  'Read data into a array

  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Resize(, 201).Value

  'Make b array at least big enough to hold the results

  ReDim b(1 To 4 * UBound(a), 1 To 203)

  'Use k to identify the starting row for each new section in the b array

  k = 1


  'For each row of data

  For i = 2 To UBound(a)

    b(k, 1) = "ACCOUNT": b(k + 1, 1) = "ADDRESS": b(k + 2, 1) = "EDETAIL": b(k + 3, 1) = "ADDRESS"

    For j = 1 To UBound(a, 2)

      Select Case j

        Case Is < 149: rowinc = 0: colinc = 0

        Case Is < 177: rowinc = 1: colinc = 0

        Case Is < 184: rowinc = 2: colinc = 0

        Case Else: rowinc = 3: colinc = 0

      End Select

      'Enter the value into the b array

      b(k + rowinc, 1 + j + colinc) = a(i, j)

    Next j

    'rowincrement k for next starting row

    k = k + 4

  Next i
 

  Application.ScreenUpdating = False

  'Create new workbook

  Set wbCSV = Workbooks.Add

  'Set range for top row of results
 
  With wbCSV.Sheets(1).Range("A2").Resize(, UBound(b, 2))

  'Enter headings & bold

    .Offset(, 1).Resize(, .Columns.Count - 1).Value = a

    .Font.Bold = True

    'Enter balance of results

    .Offset(1).Resize(UBound(b)).Value = b

    'Remove unwanted rows

    For i = UBound(b) To 1 Step -1

      If .Cells(i, 202).End(xlToLeft).Column = 1 Then .Rows(i).EntireRow.Delete

    Next i
    
    .Cells(0, 1).Value = "This is a test , STANDARD 1.0"
    .Cells(1, 1).Value = "LineType"

  End With

  'Save as CSV
    
  wbCSV.SaveAs Filename:=ThisWorkbook.Path & "\" & "Test_File_" & Format(Now, "ddmmyyyyhhmm") & ".csv", FileFormat:=xlCSV

  Application.ScreenUpdating = True

End Sub

 

[Peter_SSs]

That code runs for me so I guess it must relate to the particular data that you have compared to mine.
Are you able to upload a copy of the 'error' file (any sensitive data disguised) to DropBox or OneDrive etc and provide a shared link here so that we can investigate?

Also, whenever you are reporting/asking about a vba error, please advise which line of code is causing the error as well as the full error message.

 

[StephenCrump]

As @Peter_SSs says, it's hard to know without seeing data. I'm also not clear which number of columns is causing the problem. 201? 202?

I'm wondering whether this loop might be deleting the row when i = 1 (i.e. row 2 of the spreadsheet)

For i = UBound(b) To 1 Step -1
  If .Cells(i, 202).End(xlToLeft).Column = 1 Then .Rows(i).EntireRow.Delete
Next i

in which case you'd be inadvertently deleting wbCSV.Sheets(1).Range("A2").Resize(, UBound(b, 2)) and hence erroring when you later refer to .Cells(0, 1)

(In any event, it would be better to set the number of columns dynamically, rather than hard-coding 201, 202, and 203)

 

[sudeepvns]

Thanks Peter and Stephen.
I found the problem, one of the columns had '=' sign and for some reason it didn't liked it so it was keep throwing up the error. Learned something new.

Peter, next time I will remember to address the line number of the code where the problem arising.

Thank you again.

I have come across another issue with this file though, everytime I run this code, in the original data file one of the columns had numbers with a leading zeros, when I run this code it drops the leading zeros out in the output file, is there another piece of line need to go in this code which fixes this issue or have to do something else? If I need to create a new thread for this issue, please let me know as this topic is not directly related tot he original topic on this thread.

Thanks

 

[Peter_SSs]

Glad you resolved the above issue

one of the columns had numbers with a leading zeros, when I run this code it drops the leading zeros out in the output file, is there another piece of line need to go in this code which fixes this issue

Yes, if you know which column it is in the result file, you can fix it on the way through.
Suppose that it is column C (column 3) in the result file that should retain those leading zeros, then add in this blue code where shown.

  With wbCSV.Sheets(1).Range("A2").Resize(, UBound(b, 2))

  'Enter headings & bold

    .Offset(, 1).Resize(, .Columns.Count - 1).Value = a

    .Font.Bold = True
   
    'Set particular column to Text format
    .Columns(3).EntireColumn.NumberFormat = "@"

    'Enter balance of results

    .Offset(1).Resize(UBound(b)).Value = b

Alternatively, since the whole lot is going to be a text file I guess you could format it all as text instead.

  With wbCSV.Sheets(1).Range("A2").Resize(, UBound(b, 2))

  'Enter headings & bold

    .Offset(, 1).Resize(, .Columns.Count - 1).Value = a

    .Font.Bold = True

    'Enter balance of results in Text format

    With .Offset(1).Resize(UBound(b))
      .NumberFormat = "@"
      .Value = b
    End With
   
    'Remove unwanted rows

    For i = UBound(b) To 1 Step -1

 

[sudeepvns]

As always, you're awesome Peter.
Thanks heaps mate.

 

[Peter_SSs]

You’re welcome.