1

Topic: VBA Excel an error 1004

I welcome!
Wrote  a macro for copying from one sheet on another
Faced an error "Application-defined or object-defined error"

Option Explicit
Sub test ()
Dim i, j, s As Integer
Dim x As String
Dim arrXA () As Integer
Dim arrGodi As Variant
arrGodi = Array (25, 42, 59, 76, 93) ' columns in which the information will be searched
For s = LBound (arrGodi) To UBound (arrGodi)
' in the beginning we remember in what lines not the empty. In them on idea  the information about 
For i = 5 To 3685
Sheets ("List2").Select ' we specify sheet from where it is copied
x = ActiveSheet. Cells (i, arrGodi (s)).Value
If x <> "" Then
' we check that the array is initialized
If IsNotEmptyArray (arrXA) Then
' we expand an array on 1 element
ReDim Preserve arrXA (UBound (arrXA) + 1)
Else
' we initialize an array the first element
ReDim Preserve arrXA (0)
End If
' it is appropriated to an array cell a line where the information contains
arrXA (UBound (arrXA)) = i
End If
Next i
' now in lines remembered in an array arrXA we register value  in the finite table
For j = LBound (arrXA) To UBound (arrXA)
Sheets ("1").Select ' we specify sheet where it is interposed
ActiveSheet. Cells (arrXA (j), arrGodi (s)).Value = ""
Next j
Next s
End Sub
' function of check of existence of an array
Function IsNotEmptyArray (parArray As Variant) As Boolean
On Error Resume Next
IsNotEmptyArray = LBound (parArray) <= UBound (parArray)
End Function

Line ActiveSheet. Cells (arrXA (j), arrGodi (s)).Value = "" takes off with an error 1004. And I can not understand why
Here this line x = ActiveSheet. Cells (i, arrGodi (s)).Value transits normally
The moderator: we Learn to use tags of design of the code - FAQ

2

Re: VBA Excel an error 1004

Guiza wrote:

a line ActiveSheet. Cells (arrXA (j), arrGodi (s)).Value = "" takes off with an error 1004.

Debug. Print ActiveSheet. Name
Guiza wrote:

And I can not understand why here this line x = ActiveSheet. Cells (i, arrGodi (s)).Value transits normally

tmp = arrXA (j)
ActiveSheet. Cells (tmp, arrGodi (s)).Value = ""

?

3

Re: VBA Excel an error 1004

Guiza;
Define a rank in which the information (the help.end (xldown), but is, etc. methods). Then through copy/paste. Arrays leave for 1969 then it was hot.