Application-defined or object-defined error

sudeepvns

New Member
Joined
Dec 14, 2021
Messages
19
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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.
 
Upvote 0
Solution
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)

Rich (BB code):
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)
 
Upvote 0
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
 
Upvote 0
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.

Rich (BB code):
  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.

Rich (BB code):
  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
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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
Back
Top