Hi,

The following array code gives me an error message Run time error 1004
"Application defined or object defined error" How do I fix? Thanks -
Dean

MyArray = Worksheets("TEST").Range(Range("A2"),
Range("A100").End(xlUp))

Dim MyArray As Variant
MyArray = Worksheets("TEST").Range("A2", Range("A100").End(xlUp)).Value
MsgBox LBound(MyArray) & vbCr & UBound(MyArray)
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware




wrote in message
Hi,
The following array code gives me an error message Run time error 1004
"Application defined or object defined error" How do I fix? Thanks -
Dean

MyArray = Worksheets("TEST").Range(Range("A2"),
Range("A100").End(xlUp))

Dean,
Depending on the location of this code, the unqualified Range objects may
refer to either the active sheet (if in a standard module) or the sheet that
holds this code, neither of which MAY be Worksheets("TEST").
It is always more clear for yourself (and others, later) and VBA to qualify
them. Assuming you mean:

With Worksheets("TEST")
MyArray = .Range("A2",.Range("A100").End(xlUp))
End With
'Notice the "." before the ranges

NickHK

wrote in message
oups.com...
Hi,

The following array code gives me an error message Run time error 1004
"Application defined or object defined error" How do I fix? Thanks -
Dean

MyArray = Worksheets("TEST").Range(Range("A2"),
Range("A100").End(xlUp))

On May 3, 2:26 pm, "NickHK" wrote:
Dean,
Depending on the location of this code, the unqualified Range objects may
refer to either the active sheet (if in a standard module) or the sheet that
holds this code, neither of which MAY be Worksheets("TEST").
It is always more clear for yourself (and others, later) and VBA to qualify
them. Assuming you mean:

With Worksheets("TEST")
MyArray = .Range("A2",.Range("A100").End(xlUp))
End With
'Notice the "." before the ranges

NickHK

wrote in message

oups.com...



Hi,

The following array code gives me an error message Run time error 1004
"Application defined or object defined error" How do I fix? Thanks -
Dean

MyArray = Worksheets("TEST").Range(Range("A2"),
Range("A100").End(xlUp))- Hide quoted text -

- Show quoted text -

Hi Nick,
I have the following code. When I hard code the array with the values
the code works. When I try to make the array dynamic nothing
populates the array. Do I need to Redim the array? Is it a Ubound/
Lbound issue? I apologise as we have been over this a few times but I
cannot seem to get this to work. Dynamic arrays are proving difficult
for me to get. Please advise. Thanks for your patience.

Public Sub CreateCSV()

Dim Sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim MyArray As Variant

' MyArray = Array("23000-7000", "23000-7300", "23000-7040") THIS
WORKS

With Worksheets("TEST")
MyArray = .Range("A2", .Range("A100").End(xlUp))
End With

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("CSV").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "CSV"
For Each Sh In Sheets(MyArray())
Last = LastRow(DestSh)

With Sh.Range("A6:Q281")
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
Set DestSh = ThisWorkbook.Worksheets("CSV")
For Each Sh In Sheets(MyArray)
Last = LastRow(DestSh)

With Sh.Range("A6:Q213")
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True

End If

End Sub

Dean,
When you fill an array from a worksheet, you will get a 2-D array (even if
you only select a single column/row), not a 1-D array as you do when using
Array().

Check the:
Ubound(MyArray,1)
Ubound(MyArray,2)

NickHK

wrote in message
oups.com...
On May 3, 2:26 pm, "NickHK" wrote:
Dean,
Depending on the location of this code, the unqualified Range objects
may
refer to either the active sheet (if in a standard module) or the sheet
that
holds this code, neither of which MAY be Worksheets("TEST").
It is always more clear for yourself (and others, later) and VBA to
qualify
them. Assuming you mean:

With Worksheets("TEST")
MyArray = .Range("A2",.Range("A100").End(xlUp))
End With
'Notice the "." before the ranges

NickHK

wrote in message

oups.com...



Hi,

The following array code gives me an error message Run time error 1004
"Application defined or object defined error" How do I fix? Thanks -
Dean

MyArray = Worksheets("TEST").Range(Range("A2"),
Range("A100").End(xlUp))- Hide quoted text -

- Show quoted text -

Hi Nick,
I have the following code. When I hard code the array with the values
the code works. When I try to make the array dynamic nothing
populates the array. Do I need to Redim the array? Is it a Ubound/
Lbound issue? I apologise as we have been over this a few times but I
cannot seem to get this to work. Dynamic arrays are proving difficult
for me to get. Please advise. Thanks for your patience.

Public Sub CreateCSV()

Dim Sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long
Dim MyArray As Variant

' MyArray = Array("23000-7000", "23000-7300", "23000-7040") THIS
WORKS

With Worksheets("TEST")
MyArray = .Range("A2", .Range("A100").End(xlUp))
End With

On Error Resume Next
If Len(ThisWorkbook.Worksheets.Item("CSV").Name) = 0 Then
On Error GoTo 0
Application.ScreenUpdating = False
Set DestSh = ThisWorkbook.Worksheets.Add
DestSh.Name = "CSV"
For Each Sh In Sheets(MyArray())
Last = LastRow(DestSh)

With Sh.Range("A6:Q281")
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
Else
Set DestSh = ThisWorkbook.Worksheets("CSV")
For Each Sh In Sheets(MyArray)
Last = LastRow(DestSh)

With Sh.Range("A6:Q213")
DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
.Columns.Count).Value = .Value
End With

Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True

End If

End Sub