Vba automation error ошибка

You are missing a «:» before «A»

Range("A" & i & ":A" & LastCol - 1)

FOLLOWUP

After I went through your comments, I saw lot of errors in your code

1) You have dimmed i as Integer. This can give you an error in Excel 2007 onwards if your last row is beyond 32,767. Change it to Long I would recommend having a look at this link.

Topic: The Integer, Long, and Byte Data Types

Link: http://msdn.microsoft.com/en-us/library/aa164754%28v=office.10%29.aspx

Quote from the above link

Integer variables can hold values between -32,768 and 32,767, while Long variables can range from -2,147,483,648 to 2,147,483,647

2) You are finding the Last Column But in Which Sheet? You have to fully qualify the path Like this.

    If WorksheetFunction.CountA(Sheets("Sheet1").Cells) > 0 Then
       LastCol = Cells.Find(What:="*", After:=[A1], _
       SearchOrder:=xlByColumns, _
       SearchDirection:=xlPrevious).Column
    End If

Same is the case with

With Sheets("Sheet1")
    Set DatesRange = Range("B2" & LastCol)
End With

You are missing a DOT before Range

This is the correct way…

.Range("B2....

Also Range("B2" & LastCol) will not give you the range that you want. See the code below on how to create your range.

3) You are using a variable LastColumn but using LastCol. I would strongly advise using Option Explicit I would also recommend having a look at this link (SEE POINT 2 in the link).

Topic: To ‘Err’ is Human

Link: http://www.siddharthrout.com/2011/08/01/to-err-is-human/

4) What happens if there .CountA(Sheets("Sheet1").Cells) = 0? I would suggest you this code instead

    If WorksheetFunction.CountA(Sheets("Sheet1").Cells) > 0 Then
       LastCol = Cells.Find(What:="*", After:=[A1], _
       SearchOrder:=xlByColumns, _
       SearchDirection:=xlPrevious).Column
    Else
        MsgBox "No Data Found"
        Exit Sub
    End If

5) ActiveSheet.Rows.Count will not give you the last active row. It will give you the total number of rows in that sheet. I would recommend getting the last row of Col A which has data.

You can use this for that

With Sheets("Sheet")
    LastRow =.Range("A" & .Rows.Count).End(xlup).row
End With

Now use LastRow instead of ActiveSheet.Rows.Count You also might want to use a For Loop so that you don’t have to increment i every time. For example

For i = 1 to LastRow

6) Finally You should never use End. Reason is quite simple. It’s like Switching your Computer using the POWER OFF button. The End statement stops code execution abruptly, without invoking the Unload, QueryUnload, or Terminate event, or any other Visual Basic code. Also the Object references held (if any) by other programs are invalidated.

7) Based on your image in Chat, I believe you are trying to do this? This uses a code which doesn’t use any loops.

Option Explicit

Sub FindFill()
    Dim wsI As Worksheet, wsO As Worksheet
    Dim DatesRange As Range
    Dim LastCol As Long, LastRow As Long

    If Application.WorksheetFunction.CountA(Sheets("Sheet1").Cells) = 0 Then
        MsgBox "No Data Found"
        Exit Sub
    End If

    Set wsI = Sheets("Sheet1")
    Set wsO = Sheets("Sheet2")

    With wsI
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
        Set DatesRange = .Range("B1:" & Split(Cells(, LastCol).Address, "$")(1) & 1)

        .Columns(1).Copy wsO.Columns(1)
        DatesRange.Copy
        wsO.Range("B2").PasteSpecial xlPasteValues, _
        xlPasteSpecialOperationNone, False, True

        .Range("B2:" & Split(Cells(, LastCol).Address, "$")(1) & LastCol).Copy
        wsO.Range("C2").PasteSpecial xlPasteValues
    End With
End Sub

This is one of the most common run-time errors. By the end of this article, you will understand why that is and how to fix it. A run-time error is the type of error that occurs during the execution of the code. VBA does not know about it prior to actually running the code. There are different variations of this error; we will provide an example of each of the types below.

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

When a range reference is not correct. It could be incorrect because it’s misspelled. It could also be incorrect because it’s trying to get a range that is at an impossible value, such as row 0 or row -2. In the example below, we’re referring to row zero and we’re using the wrong syntax. VBA recognizes that we’re trying to refer to a range but it sees that the arguments (parameters) written within the range in order to identify it are written incorrectly; there should be no commas when using Range().

Sub Range_Error()
Range(0, 1).Select
End Sub

Instead, we should write the code as follows:

Sub Range_Error()
Range(“A1”).Select
End Sub

We could also run into this problem unintentionally if we are looping a variable, such as X, and it becomes zero.

In the below example, X is equal to 5 and the loop is set to keep reducing X by 1 until it reaches -1 then it would not execute. But an error will occur when X = 0.

I set the code to write ‘Correct’ in each of the cells up until the cell we are not able to insert values into. Our code will insert ‘Correct’ in Range(“A5) to Range(“A1”) then will give the error when we ask it to insert ‘Correct’ into Range(“A0”).

Note that we didn’t define X to be a String, so VBA assumes it’s a variant. This means it can be any type of data, so Excel assumes that it’s the type of data that should be utilized in the context we’re utilizing it in. In this case, X would be considered a string. But when X is zero, Excel doesn’t know that it should be string, since “A0” doesn’t exist as a range. So it treats the zero as a number, which triggers this error, because it’s syntax related, meaning what’s written inside Range() is not written correctly from a syntax point of view.

Sub Range_Error()
X = 5
Do Until X = -1
Range("A" & X) = "Correct"
X = X - 1
Loop
End Sub

To correct the problem, we need to stop our code from running the loop once X reaches 0.

Sub Range_Error()
X = 5
Do Until X = 0
Range("A" & X) = "Correct"
X = X - 1
Loop
End Sub

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

This error is shown when we are trying to give the same name to a worksheet which has already been given to another worksheet in the same workbook. Given that we already have a sheet named “Sheet1”, we create a new sheet, we click on it so that it becomes the active sheet, and we try to name it “Sheet1” by running the below code. We get an error immediately once we reach the sheet name changing line.

Sub Error_Name_Taken()
ThisWorkbook.Sheets("Sheet2").Activate
ActiveSheet.Name = "Sheet1"
End Sub

EXAMPLE 3: VBA Run Time Error 1004: Select Method of Range class failed

This error occurs when we attempt to activate a range through VBA in another worksheet (not the currently active one) without activating that worksheet first.

Sub Error_Select_Failed()
ThisWorkbook.Sheets("Sheet2").Activate
ThisWorkbook.Sheets("Sheet1").Range("A1").Select
End Sub

To correct the issue, we would need to first activate “Sheet1” first before attempting to select a range inside of it. Adding a line to active the worksheet resolves the issue.

Sub Error_Select_Failed()
ThisWorkbook.Sheets("Sheet2").Activate
ThisWorkbook.Sheets("Sheet1").Activate
ThisWorkbook.Sheets("Sheet1").Range("A1").Select
End Sub

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

This error occurs when we use VBA to open a workbook that cannot be opened. The reason that Excel is unable to open the workbook could be because it is already open, or the workbook is being used by another program at that moment. A special case exists when you attempt to open a file in read-only mode while the file is corrupt. This would give an error because Excel can open a corrupt file only if it is allowed to repair it first, which it does by writing to it. So, if you open a corrupt file in read-only mode, you are essentially preventing it from repairing the file and hence it would fail to open.

EXAMPLE 5: VBA Runtime Error 1004: file format is not valid

This error, like the one in example 5, is related to attempting to open a file. In this situation, however, we are trying to open a file that isn’t an Excel file; the file doesn’t have any of the Excel extensions ( .xlsx, .xls, .xlsb, .xlsm, etc.)

Sub error_workbook_open()
Workbooks.Open "C:\Users\mmost\Dropbox\Daniel Troha\Run-time error 1004.docx"
End Sub

The code is attempting to open a word document file with extension .docx. Using ‘Workbooks.Open’ can only be used for files that have Excel extensions. To open documents of different extensions, we will need to take a completely different approach. For example, the below code opens a word document.

Sub error_word_doc_open()
Dim wordapp
Dim strFileName As String
strFileName = "C:\Users\mmost\Dropbox \Else Without If.docx"
Set wordapp = CreateObject("word.Application")
wordapp.Documents.Open strFile
wordapp.Visible = True
End Sub

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

This error occurs whenever we are trying to open a file that does not exist at the specified path. When faced with this error, it’s important to check three aspects of the file path; file location, file name, and file extension. Any of these parameters could be wrong, so check each of them carefully.

Sub error_workbook_open()
Workbooks.Open "C:\Users\mmost\Dropbox\Another Workbook.xls"
End Sub

EXAMPLE 7: VBA Runtime Error 1004: Application-defined or Object-defined error

This error encompasses a wide range of possibilities. The error is triggered due to violating one of the rules that are used to handle the object you’re working with.

For example, in the following code, I try to select a cell that exists on row -1 and column 1. There is no such cell as the minimum row number allowed is 1, thus, VBA determines that I violated this rule and throws this error.

Note that this is different from Example 1 in that the syntax I am using here is perfectly correct, but I am violating particular rules that once violated, trigger an error. The wide range of these rules make this one of the most common errors in VBA. To resolve it, you will want to know more about the object you’re working with (in this case it’s a cell in the worksheet) and understand the rules and limitations that exist on the various values that you can use with the object.

Sub error_object_defined()
ThisWorkbook.Sheets(1).Cells(-1, 1).Select
End Sub

To wrap it up, here’s one example of where a syntax error calling a function causes this error to occur.