473,321 Members | 1,708 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,321 software developers and data experts.

Method 'Range of object '_Global' failed

Expand|Select|Wrap|Line Numbers
  1.  
  2. Sub WorkBook_Open()
  3. RemoveEmptyRow
  4. ConcatenateColumn
  5. DeleteBlankColumns
  6. SaveFile
  7. End Sub
  8.  
  9. Sub SaveFile()
  10. 'ActiveWorkbook.Save
  11. ThisWorkbook.Saved = True
  12. Application.Quit
  13. Application.ActiveWindow.Close SaveChanges:=True
  14. ActiveWorkbook.Close SaveChanges:=True
  15. 'ActiveWorkbook.SaveCopyAs ("D:\save\" & Format(Now, "ddmmyy") & ".xls")
  16. 'ActiveWorkbook.SaveAs filename:="D:\RESULT\" & Format(Now, "ddmmyy") & ".xls", FileFormat:=xlCurrentPlatformText, CreateBackup:=False
  17. 'ActiveWorkbook.SaveAs Filename:="D:\RESULT\Text.txt", FileFormat:=xlCurrentPlatformText, CreateBackup:=False
  18. End Sub
  19.  
  20. Sub RemoveEmptyRow()
  21. Dim i As Long
  22.     Dim DelRange As Range
  23.     On Error GoTo Whoa
  24.     Application.ScreenUpdating = False
  25.     'Path = ThisWorkbook.Path
  26.     'Path = ActiveWorkbook.Path
  27.     For i = 1 To 1500
  28.         If Application.WorksheetFunction.CountA(Range("A" & i & ":" & "DG" & i)) = 0 Then
  29.             If DelRange Is Nothing Then
  30.                 Set DelRange = Rows(i)
  31.             Else
  32.                 Set DelRange = Union(DelRange, Rows(i))
  33.             End If
  34.         End If
  35.     Next i
  36.     If Not DelRange Is Nothing Then DelRange.Delete Shift:=xlUp
  37. LetsContinue:
  38.     Application.ScreenUpdating = True
  39.     Exit Sub
  40. Whoa:
  41.     MsgBox Err.Description
  42.     Resume LetsContinue
  43.  ActiveWorkbook.Save
  44. End Sub
  45.  
  46. Sub ConcatenateColumn()
  47.  'Path = ActiveWorkbook.Path
  48.  'For i = A To Cells(Rows.Count, "BE").End(xlUp).Row
  49. For i = 1 To Cells(Rows.Count, "BE").End(xlUp).Row
  50.        Cells(i, "BE").Value = Cells(i, "BE").Value & Cells(i, "BF").Value
  51. Next i
  52. For i = 1 To Cells(Rows.Count, "AE").End(xlUp).Row
  53.        Cells(i, "AE").Value = Cells(i, "AE").Value & Cells(i, "AF").Value & Cells(i, "AG").Value
  54. Next i
  55. For i = 1 To Cells(Rows.Count, "G").End(xlUp).Row
  56.        Cells(i, "G").Value = Cells(i, "G").Value & Cells(i, "H").Value
  57. Next i
  58. For i = 1 To Cells(Rows.Count, "K").End(xlUp).Row
  59.         Cells(i, "K").Value = Cells(i, "K").Value & Cells(i, "L").Value
  60. Next i
  61. For i = 1 To Cells(Rows.Count, "M").End(xlUp).Row
  62.         Cells(i, "M").Value = Cells(i, "M").Value & Cells(i, "N").Value
  63. Next i
  64.  For i = 1 To Cells(Rows.Count, "AI").End(xlUp).Row
  65.         Cells(i, "AI").Value = Cells(i, "AI").Value & Cells(i, "AJ").Value & Cells(i, "AK") & Cells(i, "AL")
  66. Next i
  67.  For i = 1 To Cells(Rows.Count, "AM").End(xlUp).Row
  68.          Cells(i, "AM").Value = Cells(i, "AM").Value & Cells(i, "AN").Value & Cells(i, "AO").Value
  69. Next i
  70.  For i = 1 To Cells(Rows.Count, "AP").End(xlUp).Row
  71.          Cells(i, "AP").Value = Cells(i, "AP").Value & Cells(i, "AQ").Value & Cells(i, "AR").Value
  72. Next i
  73.  For i = 1 To Cells(Rows.Count, "AS").End(xlUp).Row
  74.          Cells(i, "AS").Value = Cells(i, "AS").Value & Cells(i, "AT").Value
  75. Next i
  76.  For i = 1 To Cells(Rows.Count, "AV").End(xlUp).Row
  77.          Cells(i, "AV").Value = Cells(i, "AV").Value & Cells(i, "AW").Value
  78. Next i
  79.  For i = 1 To Cells(Rows.Count, "BA").End(xlUp).Row
  80.          Cells(i, "BA").Value = Cells(i, "BA").Value & Cells(i, "BB").Value & Cells(i, "BC").Value
  81. Next i
  82.  For i = 1 To Cells(Rows.Count, "BL").End(xlUp).Row
  83. Cells(i, "BL").Value = Cells(i, "BL").Value & Cells(i, "BM").Value
  84. Next i
  85.  For i = 1 To Cells(Rows.Count, "BP").End(xlUp).Row
  86. Cells(i, "BP").Value = Cells(i, "BP").Value & Cells(i, "BQ").Value
  87. Next i
  88.  For i = 1 To Cells(Rows.Count, "BS").End(xlUp).Row
  89. Cells(i, "BS").Value = Cells(i, "BS").Value & Cells(i, "BT").Value
  90. Next i
  91. For i = 1 To Cells(Rows.Count, "BS").End(xlUp).Row
  92. Cells(i, "BS").Value = Cells(i, "BS").Value & Cells(i, "BT").Value
  93. Next i
  94. For i = 1 To Cells(Rows.Count, "BW").End(xlUp).Row
  95. Cells(i, "BW").Value = Cells(i, "BW").Value & Cells(i, "BX").Value
  96. Next i
  97. For i = 1 To Cells(Rows.Count, "CA").End(xlUp).Row
  98. Cells(i, "CA").Value = Cells(i, "CA").Value & Cells(i, "CB").Value
  99. Next i
  100. For i = 1 To Cells(Rows.Count, "CH").End(xlUp).Row
  101.    Cells(i, "CH").Value = Cells(i, "CH").Value & Cells(i, "CI").Value & Cells(i, "CJ").Value
  102. Next i
  103. For i = 1 To Cells(Rows.Count, "CL").End(xlUp).Row
  104. Cells(i, "CL").Value = Cells(i, "CL").Value & Cells(i, "CM").Value
  105. Next i
  106. For i = 1 To Cells(Rows.Count, "CO").End(xlUp).Row
  107. Cells(i, "CO").Value = Cells(i, "CO").Value & Cells(i, "CP").Value
  108. Next i
  109. For i = 1 To Cells(Rows.Count, "CR").End(xlUp).Row
  110. Cells(i, "CR").Value = Cells(i, "CR").Value & Cells(i, "CS").Value
  111. Next i
  112. For i = 1 To Cells(Rows.Count, "CT").End(xlUp).Row
  113. Cells(i, "CT").Value = Cells(i, "CT").Value & Cells(i, "CU").Value
  114. Next i
  115. For i = 1 To Cells(Rows.Count, "CV").End(xlUp).Row
  116. Next i
  117. End Sub
  118.  
  119. Sub DeleteBlankColumns()
  120.  'Path = ActiveWorkbook.Path
  121.  'Declaring the variable lColumn as long to store the last Column number
  122. Dim lColumn As Long
  123. 'Declaring the variable iCntr as long to use in the For loop
  124. Dim iCntr As Long
  125. 'Assigning the last Column value to the variable lColumn
  126. lColumn = 111
  127. iCntr = 1
  128. 'Using for loop
  129. 'We are checking the each cell value if it cell is 0 (equals to zero value)
  130. 'And deleting the Column if true
  131. For iCntr = lColumn To 1 Step -1
  132. If Cells(1, iCntr) = 0 Then
  133. Columns(iCntr).Delete
  134. End If
  135. Next
  136. ActiveWindow.Visible = True
  137. End Sub
  138.  
  139.  
I Want to run all script in workbook_open() which will run the macro automatically after open. But, once I open the excel file using bat file, this error coming out.
"Method 'Range of object'_Global'failed"
Please help me
Dec 30 '14 #1
3 7825
twinnyfo
3,653 Expert Mod 2GB
This thread may more properly belong in MS Excel.....

You have not told us at which line you are receiving this error. I don't see any "_Global" anywhere in your code, which makes me think it the error may be firing from somewhere else. But, I wouldn't know where to start without some more information.
Dec 30 '14 #2
The error shown at line 48. I have use personal macro vba. Personal macro script available every-time we open the excel no matter which file. In my case, I have use batch file to run the excel file which contain data. After that, I use
Expand|Select|Wrap|Line Numbers
  1. Sub Workbook_Open() 
  2.  
  3. end sub
  4.  
to auto run the script. I have call Sub SaveFile() , Sub RemoveEmptyRow(), Sub ConcatenateColumn(), and Sub DeleteBlankColumns() in Sub Workbook_Open() in order to run the script inside. I use this script to format the file because of empty rows, got some extra column which takes the data from previous column, and got blank column.
After I open the excel using batch file, the error is shown. Then. I click ok and go to developer, click macro and manually run the Workbook_open() on that data file.
Thank you for your response. Sorry for unclear question. Ask anything that you want to know to help me. I will answer. Please help.
Dec 31 '14 #3
twinnyfo
3,653 Expert Mod 2GB
Has this code always worked but now has come up with errors? Or, is this new code that you are trying to get to run properly?

Line 48 is a comment line, and has no meaning for the compiler, so it will not cause the error.

One possibility for your error may have to do with how you are referring to your cells. The Cells() property requires numeric values, not text. So
Expand|Select|Wrap|Line Numbers
  1. Cells(Rows.Count, "BE")
should probably be
Expand|Select|Wrap|Line Numbers
  1. Cells(Rows.Count, 57)
etc.

I've made similar mistakes in the past, as Excel referencing is a bit different than Excel VBA referencing.

Hope this hepps!
Dec 31 '14 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

2
by: Todd | last post by:
This has really been my day for wierd problems sorting one of my forms (a subform actually)... so here's another one. I am now receiving the following error Run-time error '-2146500594...
1
by: Jeffrey Bradshaw | last post by:
Is there any way to make a copy of a word.range object? I have a range in a document and I need to monkey around with it but I don't want to change the original in the document. I've tried...
2
by: Lauren Wilson | last post by:
Hi folks, I have an Access 2003 app that works perfectly on my computer. However, when we install it on OTHER computers that ALSO have access 2003 we get an error on startup: Error:...
9
by: windandwaves | last post by:
Hi gurus I have a class from which I create an object. I want this object to be available in all my functions for my application. Basically, I want to make the object global. What is the best...
4
by: ielamrani | last post by:
Hi, I am getting this error when I try to export to an excel sheet. When I click on a button to export the first time it's fine, I rename the exported excel sheet and I try to export it again and I...
0
by: kaushalgajjar | last post by:
Hi, I had created an active-x control (ocx) in vb6 and i have used in VB.Net and HTML using ClassId reference and the active-x was working fine with ASP.Net, HTML and VB6. Now the active-x is...
0
by: CoreyReynolds | last post by:
Hey all, I have a piece of code that dumps a bunch of data into a spreadsheet. Also rearranges it into a pivot table and then graphs the pivot table as well so my boss can get a clear view of the...
4
by: fnemo | last post by:
I'm getting the error - Method 'Item' of object 'Forms' failed . Earlier this error was not occuring. In the below code, first textboxes are created dynamically in the form "display_result"....
3
by: Hema Suresh | last post by:
Hi all I created a database via VB and saved it in excel sheet I have 10 command buttons and 10 text box controls on the vb form and i coded in the way to get the data from the user once the...
4
by: ChiomaJennifer | last post by:
hi... i have an access application in which i need to open an existing excel sheet, find a date (already in the sheet) and populate a row (with the date cell column) with either 1 or 0 this means...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.