Вба ошибка 1004

Return to VBA Code Examples

In this Article

VBA Error 1004 – Object does not exist
VBA Error 1004 – Name Already Taken
VBA Error 1004 – Incorrectly Referencing an Object
VBA Error 1004 – Object Not Found

This tutorial will explain the VBA Error 1004- Application-Defined or Object-Defined Error.

VBA run-time error 1004 is known as an Application-Defined or Object-Defined error which occurs while the code is running. Making coding errors (See our Error Handling Guide) is an unavoidable aspect learning VBA but knowing why an error occurs helps you to avoid making errors in future coding.

VBA Error 1004 – Object does not exist

If we are referring to an object in our code such as a Range Name that has not been defined, then this error can occur as the VBA code will be unable to find the name.

Sub CopyRange()
  Dim CopyFrom As Range
  Dim CopyTo As Range
  Set CopyFrom = Sheets(1).Range("CopyFrom")
  Set CopyTo = Sheets(1).Range("CopyTo")
  CopyFrom.Copy
CopyTo.PasteSpecial xlPasteValues
End Sub

The example above will copy the values from the named range “CopyFrom” to the named range “CopyTo” – on condition of course that these are existing named ranges! If they do not exist, then the Error 1004 will display.

The simplest way to avoid this error in the example above is to create the range names in the Excel workbook, or refer to the range in the traditional row and column format eg: Range(“A1:A10”).

VBA Error 1004 – Name Already Taken

The error can also occur if you are trying to rename an object to an object that already exists – for example if we are trying to rename Sheet1 but the name you are giving the sheet is already the name of another sheet.

Sub NameWorksheet()
  ActiveSheet.Name = "Sheet2"
End Sub

If we already have a Sheet2, then the error will occur.

VBA Error 1004 – Incorrectly Referencing an Object

The error can also occur when you have incorrectly referenced an object in your code. For example:

Sub CopyRange()
  Dim CopyFrom As Range
  Dim CopyTo As Range
  Set CopyFrom = Range("A1:A10")
  Set CopyTo = Range("C1:C10")
  Range(CopyFrom).Copy
  Range(CopyTo).PasteSpecial xlPasteValues
End Sub

This will once again give us the Error 10004

Correct the code, and the error will no longer be shown.

Sub CopyRange()
  Dim CopyFrom As Range
  Dim CopyTo As Range
  Set CopyFrom = Range("A1:A10")
  Set CopyTo = Range("C1:C10")
  CopyFrom.Copy
  CopyTo.PasteSpecial xlPasteValues
End Sub

VBA Error 1004 – Object Not Found

This error can also occur when we are trying to open a workbook and the workbook is not found – the workbook in this instance being the object that is not found.

Sub OpenFile()
 Dim wb As Workbook
 Set wb = Workbooks.Open("C:\Data\TestFile.xlsx")
End Sub

Although the message will be different in the error box, the error is still 1004.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

Learn More!

Источник

Four ways to fix runtime error 1004 in Excel:

Workable Solutions	Step-by-step Troubleshooting
Fix 1. Delete the GWXL97.XLA Files	Fix the Excel error 1004 is to find and delete the error file. Go to C:\Program Files\MS Office\Office\XLSTART…Full steps
Fix 2. Check the Trust Access to the VBA Project Object Model	Enable a VBA project trust option in Excel Trust Center to fix Excel error 1004. Open a blank Excel file…Full steps
Fix 3. Create Another Excel Template	Start a new Excel workbook and make sure there is only one worksheet in it. Format the workbook first…Full steps
Fix 4. Repair Corrupted Excel File	Repair corrupted Excel files with a file recovery tool. EaseUS file repair tool fixes severely corrupted XLS and XLSX files and retrieves everything from Excel…Full steps

Microsoft Visual Basic for Applications (VBA) is developed to help users write programs for the Windows operating system. It runs as an internal programming language in Microsoft Office, such as Word, Excel, and PowerPoint.

Some users have reported that when running VBA in an Excel chart or trying to generate a Macro in Excel documents, an error message popped up saying: Runtime error 1004. And then they find themselves cannot access the Excel files. If you have the same encounter as these users, this post is the right place for you. You can find both the reasons and the corresponding solutions of this error code on this page.

Runtime Error Details

The error message contains more information than the error code 1004. Generally, follow the error code, you can see a brief description. The most repeated error messages are listed below:

Runtime error 1004: Application or object-defined error.
Runtime error 1004: Method Ranger of Object Worksheet failed.
Runtime error 1004: Copy Method of Worksheet Class failed.

The Reason Why You See Runtime Error 1004 in Excel

If you want to know how to fix runtime error 1004 in Excel properly, you need to understand what leads to this issue. The following are the most prominent reasons.

Macro Name Error

The Macro you are running is copying the original worksheet to a workbook with a defined name that you did not save and close before running the Macro.

File Conflict

When opening the VBA Excel file, it gets conflicted with other programs.

Too Many Legend Entries

The Excel chart contains more legend entries than space available to display the legend entries on the chart.

Excel File Corruption

Your .xls files got corrupted, infected, or damaged.

Although many reasons would cause this Excel error 1004 problem, luckily, some valid methods can help users re-access the files. Let’s check them one by one.

Fix 1. Delete the GWXL97.XLA Files to Fix Runtime Error 1004 in Excel

The easiest method to fix the Excel error 1004 is to find and delete the error file.

Step 1. Go to C:\Program Files\MS Office\Office\XLSTART.

Step 2. Find GWXL97.XLA file and delete it.

Step 3. Reopen your Excel file and check if the problem is solved.

Fix 2. Check the Trust Access to the VBA Project Object Model

Another solution you can try is to enable a VBA project trust option in Excel Trust Center. Follow the detailed steps and have a try.

Step 1. Open a blank Excel file and click «Files» on the upper left.

Step 2. Click Option and go to Trust Center.

Step 3. Find and enter the Trust Center Settings.

Step 4. Under Macro Settings, tick the option of «Trust access to the VBA project object model.»

Now you can check your Excel file.

Fix 3. Create Another Excel Template to Fix Runtime Error 1004 in Excel

This method could be a little bit complicated, but it’s useful and worth trying.

Step 1. Please start a new Excel workbook and make sure there is only one worksheet in it.

Step 2. Format the workbook first and then put the data you need onto it.

Step 3. Tap File > Save As, first enter the file name, and click the unfold arrow in Save as Type column.

Excel 2003: Choose Excel 97-2003 Template.

Excel 2007 or Later: Choose Excel Template.

Step 4. Click «Save» to confirm.

Now you can insert it programmatically by using the following code: Add Type:=path\filename. The file name is the one you set when you create the new Excel template.

Fix 4. Repair Corrupted Excel Files Due to Error 1004

If all the above solutions can’t help you out, then there is one possibility that the Excel file you want to open is damaged. To fix a damaged Excel file, you can rely on file repair software. EaseUS Fixo Document Repair is a great choice.

With this tool, click the «Repair» button and wait for it to fix all the corrupted documents for you.

Repair various corrupted files, including repairing Word, Excel, and PDF document
Fix unreadable contents in Word efficiently
Repair corrupted PDF files, extract the text, comments, labels, graphics, etc.
Compatible with Microsoft Office 2019, 2016, 2013, 2010, & previous versions.

Download the software and follow the detailed steps below to fix corrupted Excel files.

Step 1. Download and launch Fixo on your PC or laptop. Choose «File Repair» to repair corrupted Office documents, including Word, Excel, PDF, and others. Click «Add Files» to select corrupted documents.

Step 2. To repair multiple files at once, click the «Repair All» button. If you want to repair a single document, move your pointer to the target file and choose «Repair». You can also preview the documents by clicking the eye icon.

Step 3. Click «Save» to save a selected document. Select «Save All» to save all the repaired files. Choose «View Repaired» to locate the repaired folder.

The Bottom Line

After reading, you must have a thorough understanding of how to fix Runtime error 1004. If you can make sure that the Excel file you want to open is valid, then the first three methods would help you out.

Once you got a damaged Excel file, a professional file recovery tool is a wiser choice. EaseUS Fixo is highly recommended by many users & IT professionals to help you repair Word, Excel, PowerPoint, and PDF files.

Источник

Excel VBA Ошибка 1004

Excel VBA Ошибка 1004

Ошибка VBA 1004 — это ошибка, с которой мы сталкиваемся при выполнении кода в VBA. Он также известен как ошибка времени выполнения VBA. Когда мы работаем в VBA или на любом другом языке программирования или даже в нашей повседневной работе, мы сталкиваемся с различными видами ошибок. Иногда даже мы пропускаем один символ в коде, что приводит к тому, что весь код не работает или, возможно, весь код неверен.

Ошибки, безусловно, являются частью кода, который мы пишем. Это может быть непреднамеренным, но они существуют. Независимо от того, насколько мы профессиональны в кодировании, ошибки во время выполнения могут возникнуть где угодно. Как объяснено выше VBA 1004 Ошибка — это ошибка, которая возникает во время выполнения кода в Excel. Это также называется ошибкой приложения или объекта.

Существуют различные типы причин, по которым мы получаем VBA Runtime Error 1004 в Excel, давайте рассмотрим некоторые из них.

Ошибка выполнения VBA 1004: сбой метода «Диапазон» объекта «_ Global»:

Эта ошибка возникает, когда значение диапазона, которое мы ссылаемся на VBA, неверно. Он также называется метод «Диапазон» объекта «_ Global» не удалось.

Ошибка выполнения VBA 1004: это имя уже занято. Попробуйте другой:

Мы присваиваем одно и то же имя рабочему листу, который уже занесен другим рабочим листом.

Ошибка выполнения VBA 1004: невозможно получить свойство select класса Range:

Это ошибка, когда мы выбираем диапазон на другом листе, не активируя тот лист, на который мы ссылаемся.

Ошибка выполнения VBA 1004: сбой метода «Открыть» объекта «Книги»:

Эта ошибка возникает, когда мы пытаемся открыть рабочую книгу, которая уже открыта, или файл уже используется другой программой.

Ошибка выполнения VBA 1004: извините, мы не смогли найти:

Мы получаем эту ошибку, когда пытаемся открыть лист, который не существует.

Как мы узнали, могут быть разные причины, по которым мы получаем ошибку во время выполнения. Ошибка выполнения может возникнуть в любой строке кода. Нам нужно научиться обрабатывать эти ошибки, и это называется VBA Error Handling.

Пример VBA Runtime Ошибка 1004 в Excel

Теперь, когда я описал различные типы ошибок, которые могут возникать во время выполнения любого кода VBA, теперь давайте изучим их, как они выглядят на примерах.

Вы можете скачать этот шаблон Excel с ошибкой VBA 1004 здесь — Шаблон Excel с ошибкой VBA 1004

Ошибка выполнения VBA 1004 — Пример № 1

Как объяснено об этой ошибке, эта ошибка возникает, когда мы ссылаемся на неверное значение именованного диапазона в VBA. Это может произойти, если мы допустим орфографическую ошибку именованного диапазона, чтобы сослаться на диапазон, который даже не существует. Чтобы продемонстрировать это, давайте сначала создадим именованный диапазон. У меня есть следующие данные здесь.

Давайте назовем этот заголовок таблицы как DATA .

Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.

Объявите подфункцию, чтобы начать писать код.

Код:

 Sub Sample () End Sub

Вызовите заголовок, который мы назвали следующим кодом, написанным ниже.

Код:

 Sub Sample () Range ("Данные"). Выберите End Sub

Когда мы запускаем код, мы видим в Excel, что он был выбран, поскольку мы правильно вызвали заголовок.

Теперь мы неправильно написали орфографию имени заголовка.

Код:

 Sub Sample () Range ("Dataa"). Выберите End Sub

Запустите код еще раз, чтобы увидеть результат.

Мы получаем Excel VBA Runtime Ошибка 1004, потому что мы неправильно написали имя диапазона.

Ошибка выполнения VBA 1004 — Пример № 2

Мы получаем эту ошибку, когда пытаемся переименовать лист с именем, которое уже занято. Например, я переименовал лист 1 в « Ананд », и я постараюсь переименовать лист 2 в тот же, чтобы увидеть результат.

Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
Объявите подфункцию, чтобы начать писать код.

Код:

 Sub Sample1 () End Sub

Попробуйте переименовать лист 2 в Anand с помощью следующего кода ниже,

Код:

 Sub Sample1 () Worksheets ("Sheet2"). Name = "Anand" End Sub

Запустите приведенный выше код и посмотрите результат.

Когда я пытаюсь переименовать лист с именем, которое уже занято, я получаю сообщение об ошибке.

Ошибка выполнения VBA 1004 — Пример № 3

Я попытаюсь добавить значение из листа 2 к переменной на листе 3. Но я не буду активировать лист 2 и посмотрю, что произойдет.

Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
Объявите подфункцию, чтобы начать писать код.

Код:

 Sub Sample2 () End Sub

Объявите две переменные A и B как целое число.

Код:

 Sub Sample2 () Dim A As Integer Dim B As Integer End Sub

В переменной B сохраните значение A в дополнение к ячейке A1 листа 2.

Код:

 Sub Sample2 () Dim A As Integer Dim B As Integer B = A + Рабочие листы ("Sheet2"). Диапазон ("A1"). Выберите End Sub

Давайте предположим, что код работает, и используем функцию msgbox для отображения значения B.

Код:

 Sub Sample2 () Dim A As Integer Dim B As Integer B = A + Рабочие листы ("Sheet2"). Диапазон ("A1"). Выберите MsgBox B End Sub

Запустите код, чтобы увидеть полученный результат.

Мы получаем эту ошибку, потому что мы не активировали лист 2, но мы пытаемся использовать значение листа 2.

Ошибка выполнения VBA 1004 — Пример № 4

Мы сталкиваемся с этой ошибкой во время выполнения, когда у нас уже открыто одно и то же имя рабочей книги, но мы пытаемся открыть ее снова.

В этом примере я уже переименовал свою книгу в VBA 1004 Error.xlsm, и я попытаюсь снова открыть ее, которая уже открыта, и посмотреть, получу ли я ошибку VBA 1004.

Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
Объявите подфункцию, чтобы начать писать код.

Код:

 Sub Sample3 () End Sub

Объявите переменную в качестве рабочей книги.

Код:

 Sub Sample3 () Dim A As Workbook End Sub

Попробуйте открыть книгу, которую мы уже открыли, с помощью следующего кода.

Код:

 Sub Sample3 () Dim A As Set Workbook Set wb = Workbooks.Open ("\\ VBA 1004 Error.xlsm", ReadOnly: = True, CorruptLoad: = xlExtractData) End Sub

Запустите приведенный выше код, чтобы увидеть результат.

Мы получаем эту ошибку, потому что мы уже открыли ту же книгу.

Ошибка выполнения VBA 1004 — Пример № 5

Мы получаем эту ошибку, когда пытаемся открыть книгу, которой не существует. Это в некоторой степени похоже на приведенную выше ошибку, поскольку VBA не может найти книгу.

Перейдите на вкладку Разработчик, нажмите на Visual Basic, чтобы открыть VB Editor.
Объявите подфункцию, чтобы начать писать код.

Код:

 Sub Sample4 () End Sub

Попробуйте открыть любую книгу с помощью следующего кода,

Код:

 Sub Sample4 () Workbooks.Open Filename: = "C: \ EDUCBA Content \ April \ VBA OR Function.xlsm" End Sub

Я уже удалил лист с места.
Запустите код, чтобы увидеть результат.

Поскольку лист не существует в данном месте, мы получаем эту ошибку.

То, что нужно запомнить

Всегда проверяйте на наличие орфографических ошибок.
Не переименовывайте несколько листов с одинаковыми именами.
Перед звонком по любой другой ссылке обязательно активируйте соответствующий лист.
Прежде чем пытаться открыть любой другой лист, убедитесь, что указан правильный путь.

Рекомендуемые статьи

Это было руководство к VBA 1004 Ошибка. Здесь мы обсудили Excel VBA Runtime Error 1004 вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

Добавить или удалить панель ошибок в Excel
VBA Пока Loop | MS Excel
Понимание ошибок в Excel
Использование IFError в VBA

Источник

I am having an issue with a Error 1004 «Application-defined or Object-defined error» when selecting a range.

I am still able to select rows (ie Rows("21:21").select) and to select ranges in other sheets of the same workbook. I do not believe the error is in the code. Maybe its some setting I am unaware of?

I have used the exact same code many times before but for some reason I cannot make it function in this sub (I have commented where the error occurs)…

Sub CopySheet1_to_PasteSheet2()

    Dim CLastFundRow As Integer
    Dim CFirstBlankRow As Integer

    'Finds last row of content
    Windows("Excel.xlsm").Activate
    Sheets("Sheet1").Activate
    Range("C21").Select
         '>>>Error 1004 "Application-defined or Object-defined error" Occurs
    Selection.End(xlDown).Select
    CLastFundRow = ActiveCell.Row
    'Finds first row without content
    CFirstBlankRow = CLastFundRow + 1

    'Copy Data
    Range("A21:C" & CLastFundRow).Select
    Selection.Copy
    'Paste Data Values
    Sheets("PalTrakExport PortfolioAIdName").Select
    Range("A21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    'Bring back to top of sheet for consistancy
    Range("A21").Select
    Range("A1").Select
End Sub

I need to get all fancy in my copying as the amount of rows will change frequently. Again, the below code has been used before without error… but not in this instance.

Dim CLastFundRow As Integer
Dim CFirstBlankRow As Integer

'Finds last row of content
Windows("Excel.xlsm").Activate
Sheets("Sheet1").Activate
Range("C21").Select
     '>>>Error 1004 "Application-defined or Object-defined error" Occurs
Selection.End(xlDown).Select
CLastFundRow = ActiveCell.Row
'Finds first row without content
CFirstBlankRow = CLastFundRow + 1

MackM

2,9065 gold badges31 silver badges45 bronze badges

asked Jul 31, 2013 at 20:58

Perhaps your code is behind Sheet1, so when you change the focus to Sheet2 the objects cannot be found? If that’s the case, simply specifying your target worksheet might help:

Sheets("Sheet1").Range("C21").Select

I’m not very familiar with how Select works because I try to avoid it as much as possible :-). You can define and manipulate ranges without selecting them. Also it’s a good idea to be explicit about everything you reference. That way, you don’t lose track if you go from one sheet or workbook to another. Try this:

Option Explicit

Sub CopySheet1_to_PasteSheet2()

    Dim CLastFundRow As Integer
    Dim CFirstBlankRow As Integer
    Dim wksSource As Worksheet, wksDest As Worksheet
    Dim rngStart As Range, rngSource As Range, rngDest As Range

    Set wksSource = ActiveWorkbook.Sheets("Sheet1")
    Set wksDest = ActiveWorkbook.Sheets("Sheet2")

    'Finds last row of content
    CLastFundRow = wksSource.Range("C21").End(xlDown).Row
    'Finds first row without content
    CFirstBlankRow = CLastFundRow + 1

    'Copy Data
    Set rngSource = wksSource.Range("A2:C" & CLastFundRow)

    'Paste Data Values
    Set rngDest = wksDest.Range("A21")
    rngSource.Copy
    rngDest.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    'Bring back to top of sheet for consistancy
    wksDest.Range("A1").Select

End Sub

answered Jul 31, 2013 at 22:58

Frank H.Frank H.

87611 silver badges21 bronze badges

It’s a bit late but might be helpful for future reference. I had just had the same issue and I think it’s because the macro was placed at the worksheet level. Right click on the modules node on the VBA project window, click on «Insert» => «Module», then paste your macro in the new module (make sure you delete the one recorded at the worksheet level).

ZygD

22.2k40 gold badges80 silver badges102 bronze badges

answered May 30, 2014 at 4:58

KamKam

2713 silver badges2 bronze badges

I as well had the same problem and nearly drove crazy. The solution was pretty unexpected.

My Excel is shipped out by default that I enter formulas in an Excel-Cell as followed:

=COUNTIF(Range; Searchvalue)
=COUNTIF(A1:A10; 7) 'Example

Please note, that parameters are separated with a semicolon;. Now if you paste exactly that string into a formula within VBA, for example like:

Range("C7").FormulaArray = "=COUNTIF(A1:A10; 7)" 'this will not work

You will get this 1004-error with absolutely no explanation. I spent hours to debug this.. All you have to do is replace all semicolon with commas.

Range("C7").FormulaArray = "=COUNTIF(A1:A10, 7)" 'this works

answered Jul 5, 2018 at 5:55

Zim84Zim84

3,4042 gold badges35 silver badges40 bronze badges

The same thing happened to me. In my case most of the worksheet was in protected mode (though the cells relevant to the macro were unlocked). When I disabled the protection on the worksheet, the macro worked fine…it seems VBA doesn’t like locked cells even if they are not used by the macro.

answered May 30, 2014 at 12:38

EmilyEmily

911 silver badge1 bronze badge

You need to find out the actual reason for this common error code: 1004. Edit your function/VBA code and run your program in debug mode to identify the line which is causing it. And then, add below piece of code to see the error,

On Error Resume Next
//Your Line here which causes 1004 error
If Err.Number > 0 Then
  Debug.Print Err.Number & ":" & Err.Description
End If

Note:
Debug shortcut keys i use in PC:
Step Into (F8), Step Over (Shift + F8), Step Out (Ctrl + Shift + F8)

answered Apr 16, 2018 at 4:08

Some operations in Excel are limited by available Memory. If you repeat the same process over and over it could produce a memory overflow and excel will not be able to repeat it anymore. This happened to me while trying to create several sheets in the same workbook.

answered Mar 29, 2014 at 13:36

You may receive a «Run-time error 1004» error message when you programmatically set a large array string to a range in Excel 2003

In Office Excel 2003, when you programmatically set a range value with an array containing a large string, you may receive an error message similar to the following:

Run-time error ‘1004’. Application-defined or operation-defined error.

This issue may occur if one or more of the cells in an array (range of cells) contain a character string that is set to contain more than 911 characters.

To work around this issue, edit the script so that no cells in the array contain a character string that holds more than 911 characters.

For example, the following line of code from the example code block below defines a character string that contains 912 characters:

Sub XLTest()
Dim aValues(4)

  aValues(0) = "Test1"
  aValues(1) = "Test2"
  aValues(2) = "Test3"

  MsgBox "First the Good range set."
  aValues(3) = String(911, 65)

  Range("A1:D1").Value = aValues

  MsgBox "Now the bad range set."
  aValues(3) = String(912, 66)
  Range("A2:D2").Value = aValues

End Sub

Other versions of Excel or free alternatives like Calc should work as well.

answered May 30, 2016 at 11:19

Cees TimmermanCees Timmerman

17.7k11 gold badges91 silver badges124 bronze badges

I could remove the error (Run-time error ‘1004’. Application-defined or operation-defined error) by defining the counters as Single

answered Jan 6, 2017 at 12:47

You can use the following code (For example if one was to want to copy cell data from Sheet2 to Sheet1).

Sub Copy
Worksheets("Sheet1").Activate                    
Worksheets("Sheet1").Range(Cells(i, 6), Cells(i, FullPathLastColumn)).Copy_
Destination:=Worksheets("Sheet2").Cells(Path2Row, Path2EndColumn + 1)
End Sub

answered Aug 31, 2016 at 13:20

I had a similar issue, but it turns out I was just referencing a cell which was off the page {i.e. cells(i,1).cut cells (i-1,2)}

answered Mar 20, 2017 at 15:07

I had a similar problem & fixed it applying these steps:

Unprotecting the sheet that I want to edit
Changing the range that I had selected by every single cell in the range (exploded)

I hope this will help someone.

batman

1,9612 gold badges22 silver badges41 bronze badges

answered Aug 9, 2016 at 3:50

You have to go to the sheet of db to get the first blank row, you could try this method.

Sub DesdeColombia ()    
  Dim LastRowFull As Long

  'Here we will define the first blank row in the column number 1 of sheet number 1:
  LastRowFull = Sheet1.Cells(Rows.Count,1).End(xlUp).Offset(1,0).Row

  'Now we are going to insert information
  Sheet1.Cells(LastRowFull, 1).Value = "We got it"    
End Sub

answered Jul 28, 2016 at 14:39

I had this issue during VBA development/debugging suddenly because some (unknown to me) function(ality) caused the cells to be locked (maybe renaming of named references at some problematic stage).

Unlocking the cells manually worked fine:

Selecting all worksheet cells (CTRL+A) and unlock by right click -> cell formatting -> protection -> [ ] lock (may be different — translated from German: Zellen formatieren -> Schutz -> [ ] Gesperrt)

answered Jun 5, 2019 at 8:50

Andreas CovidiotAndreas Covidiot

4,3265 gold badges51 silver badges97 bronze badges

I had a similar issue when trying to loop on each sheet of a workbook.
To resolve it I did something like this

dim mySheet as sheet

for each mysheet in myWorkbook.sheets

    mySheet.activate
    activesheet.range("A1").select

    with Selection
    'any wanted operations here
    end with

next

And it worked just fine

I hope you can adapt it in your situation

Mogsdad

44.8k21 gold badges153 silver badges275 bronze badges

answered May 4, 2016 at 21:27

I ran into the same issue and found that the individual that created the worksheet had several columns locked. I removed the protection and everything worked as designed.

answered Feb 20, 2017 at 18:04

I am also having the same problem and I solved by as below.
in macro have a variable called rownumber and initially i set it as zero. this is the error because no excel sheet contains row number as zero. when i set as 1 and increment what i want.
now its working fine.

answered Feb 14, 2018 at 5:16

SingaravelanSingaravelan

8093 gold badges19 silver badges32 bronze badges

I also had a similar issue. After copying and pasting to a sheet I wanted the cursor/ selected cell to be A1 not the range that I just pasted into.

Dim wkSheet as Worksheet
Set wkSheet = Worksheets(<sheetname>)

wkSheet("A1").Select

but got a 400 error which was actually a 1004 error

You need to activate the sheet before changing the selected cell
this worked

Dim wkSheet as Worksheet
Set wkSheet = Worksheets(<sheetname>)

wkSheet.Activate
wkSheet("A1").Select

answered Apr 7, 2018 at 11:10

Dave PileDave Pile

5,5593 gold badges34 silver badges49 bronze badges

Just wanted to add an additional fix since I had previously never encountered it:

A user of mine was running into this runtime error with an Excel workbook he pulls information from, but only on his desktop computer even though its hardware and software were nearly identical to his laptop (same amount of memory, OS, Office configuration, etc.).

It turned out that the monitor connected to his desktop computer was too big — the solution/work around was to open a blank Excel file, make the Excel window smaller, then open the file he was having issues with.

Since this was a file generated by a third-party — we could not edit/implement any of the previously suggested fixes, nor did changing the macro or protection settings do anything (and they were working on his laptop with the same settings anyway).

answered Jul 17, 2021 at 17:21

mael’mael’

4533 silver badges7 bronze badges

Источник

Excel VBA Error 1004

VBA 1004 Error is an error we encounter while we execute a code in VBA it is also known as VBA Runtime error. While we work in VBA or in any other programming language or even in our daily work we encounter different kinds of errors. Sometimes even we miss a single character in the code which causes the whole code not to work or maybe the entire code is wrong.

Errors are definitely a part of the code we write. It may be unintentional but they exist. No matter how pro we are in coding, runtime error can occur anywhere. As explained above VBA 1004 Error is an error which occurs during the runtime of the code in excel. It is also called an application defined or object defined error.

There are different types of reasons we get VBA Runtime Error 1004 in excel, let us learn a few of them.

VBA Runtime Error 1004: Method ‘Range’ of object ‘_ Global’ failed:

This error occurs when the range value we refer to VBA is incorrect. It is also called as Method “Range” of object’ _ Global’ failed.

VBA Run Time Error 1004: That Name is already taken. Try a different One:

We give the same name to a worksheet which is already taken by another worksheet.

VBA Runtime Error 1004: Unable to get the select property of Range class:

This is an error when we select a range in another worksheet without activating the worksheet we are referring to.

VBA Runtime Error 1004: Method ‘Open’ of object ‘Workbooks’ failed:

This error occurs when we try to open a workbook which is already open or the file is used by another program already.

VBA Runtime Error 1004: Sorry We Couldn’t Find:

We get this error when we try to open a worksheet which doesn’t exist.

As we have learned there can be various reasons we get a runtime error. Runtime error can occur at any line of code. We need to learn how to learn to handle these errors and it is called VBA Error Handling.

Example of VBA Runtime Error 1004 in Excel

Now as I have described different types of error which can occur during runtime of any VBA code now let us learn them how they appear with examples.

You can download this VBA 1004 Error Excel Template here – VBA 1004 Error Excel Template

VBA Runtime Error 1004 – Example #1

As explained about this error, this error occurs when we refer to an incorrect named range value in VBA. This can happen if we make a spelling mistake of the named range of to refer a range that doesn’t even exist. To demonstrate this let us make a named range first. I have the following data here.

Let us name this table header as DATA.

Go to the Developer tab click on Visual Basic to Open VB Editor.

Declare the sub-function to start writing the code.

Code:

Sub Sample()

End Sub

Call the header we named by the following code written below.

Code:

Sub Sample()

Range("Data").Select

End Sub

When we run the code we can see in the excel that it has been selected as we have called the header correctly.

Now we misspell the spelling for the header name.

Code:

Sub Sample()

Range("Dataa").Select

End Sub

Run the code again to see the result.

We get excel VBA Runtime Error 1004 because we have misspelled the range name.

VBA Runtime Error 1004 – Example #2

We get this error when we try to rename a worksheet with a name which is already taken. For example, I have renamed sheet 1 as “Anand” and I will try to rename sheet 2 as same then see the result.

Go to the Developer tab click on Visual Basic to Open VB Editor.
Declare a sub-function to start writing the code.

Code:

Sub Sample1()

End Sub

Try to rename sheet 2 as Anand by the following code below,

Code:

Sub Sample1()

Worksheets("Sheet2").Name = "Anand"

End Sub

Run the above code and see the result.

When I try to rename a sheet with the name which is already taken I get an Error.

VBA Runtime Error 1004 – Example #3

I will try to add the value from sheet 2 to a variable in sheet 3. But I will not activate the sheet 2 and see what happens.

Go to the Developer tab click on Visual Basic to Open VB Editor.
Declare a sub-function to start writing the code.

Code:

Sub Sample2()

End Sub

Declare two variables A and B as an integer.

Code:

Sub Sample2()

Dim A As Integer
Dim B As Integer

End Sub

In Variable B store the value of A in addition to cell A1 of sheet 2.

Code:

Sub Sample2()

Dim A As Integer
Dim B As Integer
B = A + Worksheets("Sheet2").Range("A1").Select

End Sub

Let us suppose the code works and use msgbox function to display the value of B.

Code:

Sub Sample2()

Dim A As Integer
Dim B As Integer
B = A + Worksheets("Sheet2").Range("A1").Select
MsgBox B

End Sub

Run the code to see the result obtained.

We get this Error because we have not activated sheet 2 but we are trying to use a value of sheet 2.

VBA Runtime Error 1004 – Example #4

We encounter this runtime error when we have already the same name of workbook open but we try to open it again.

For this example, I have already renamed my workbook as VBA 1004 Error.xlsm and I will try to open it again which is already open and see if I get VBA 1004 Error.

Go to the Developer tab click on Visual Basic to Open VB Editor.
Declare a sub-function to start writing the code.

Code:

Sub Sample3()

End Sub

Declare a variable as the workbook.

Code:

Sub Sample3()

Dim A As Workbook

End Sub

Try to open the workbook we have currently already open with the following code.

Code:

Sub Sample3()

Dim A As Workbook

Set wb = Workbooks.Open("\\VBA 1004 Error.xlsm", ReadOnly:=True, CorruptLoad:=xlExtractData)

End Sub

Run the above code to see the result.

We get this error because we have already opened the same workbook already.

VBA Runtime Error 1004 – Example #5

We get this error when we try to open a workbook which doesn’t exist. This is somewhat similar to the above error we get as VBA cannot find the workbook.

Go to the Developer tab click on Visual Basic to Open VB Editor.
Declare a sub-function to start writing the code.

Code:

Sub Sample4()

End Sub

Try Open any workbook with the following code,

Code:

Sub Sample4()

Workbooks.Open Filename:="C:\EDUCBA Content\April\VBA OR Function.xlsm"

End Sub

I have already deleted the sheet from the location.
Run the code to see the result.

As the sheet doesn’t exist at the given location we get this error.

Things to Remember

Always check for spelling mistakes.
Do not rename multiple worksheets with the same name.
Before calling any other reference to be sure to activate the respective worksheet.
Before trying to open any other worksheet ensure the path provided is correct.

VBA Error 1004 – Object does not exist

VBA Error 1004 – Name Already Taken

VBA Error 1004 – Incorrectly Referencing an Object

VBA Error 1004 – Object Not Found

VBA Coding Made Easy

Runtime Error Details

The Reason Why You See Runtime Error 1004 in Excel

Fix 1. Delete the GWXL97.XLA Files to Fix Runtime Error 1004 in Excel

Fix 2. Check the Trust Access to the VBA Project Object Model

Fix 3. Create Another Excel Template to Fix Runtime Error 1004 in Excel

Fix 4. Repair Corrupted Excel Files Due to Error 1004

The Bottom Line

Excel VBA Ошибка 1004

Пример VBA Runtime Ошибка 1004 в Excel

Ошибка выполнения VBA 1004 — Пример № 1

Ошибка выполнения VBA 1004 — Пример № 2

Ошибка выполнения VBA 1004 — Пример № 3

Ошибка выполнения VBA 1004 — Пример № 4

Ошибка выполнения VBA 1004 — Пример № 5

То, что нужно запомнить

Рекомендуемые статьи

Excel VBA Error 1004

Example of VBA Runtime Error 1004 in Excel

VBA Runtime Error 1004 – Example #1

VBA Runtime Error 1004 – Example #2

VBA Runtime Error 1004 – Example #3

VBA Runtime Error 1004 – Example #4

VBA Runtime Error 1004 – Example #5

Things to Remember

Recommended Articles

Интересное по теме: