Assing a dynamic range to an array
 

I have a sheet populated with a changing list of names. I am trying to
create code which will assign those names to a dynamic array.
The code looks for the cell with the value "Mechanics:" and then I
want it to assign the groups of mechanics names in the changing range
of names directly below the found cell to an array.

I have the following code snippet which I am having troubles with:

With Worksheets("Index")
Set c = .Cells.Find("Mechanics:", LookIn:=xlValues)
If Not c Is Nothing Then
MechNameStart = c.Offset(1, 0)
MechNameEnd = MechNameStart.End(xlDown)
End If
End With

I am getting Run-time error '424': Object required.
What I would like to do then is assign the entire range from the start
(MechNameStart) to the end (MechNameEnd) to an array.

Thanks in advance for any assistance...

Assing a dynamic range to an array
 

Sorry, didn't mean to call it Assing (Assigning) :p

On Jan 23, 5:55*pm, " wrote:
I have a sheet populated with a changing list of names. I am trying to
create code which will assign those names to a dynamic array.
The code looks for the cell with the value "Mechanics:" and then I
want it to assign the groups of mechanics names in the changing range
of names directly below the found cell to an array.

I have the following code snippet which I am having troubles with:

* * With Worksheets("Index")
* * * * * * * * Set c = .Cells.Find("Mechanics:", LookIn:=xlValues)
* * * * * * * * If Not c Is Nothing Then
* * * * * * * * * * MechNameStart = c.Offset(1, 0)
* * * * * * * * * * MechNameEnd = MechNameStart.End(xlDown)
* * * * * * * * End If
* * End With

I am getting Run-time error '424': Object required.
What I would like to do then is assign the entire range from the start
(MechNameStart) to the end (MechNameEnd) to an array.

Thanks in advance for any assistance...

Assing a dynamic range to an array
 

With Worksheets("Index")
Set c = .Cells.Find("Mechanics:", LookIn:=xlValues)
If Not c Is Nothing Then
MechNameStart = c.Offset(1, 0)
MechNameEnd = MechNameStart.End(xlDown)
End If
MyArray = Application.Transpose(c.Resize(MechNameEnd.Row -
MechNameStart + 1))
End With


--
__________________________________
HTH

Bob

wrote in message
...
I have a sheet populated with a changing list of names. I am trying to
create code which will assign those names to a dynamic array.
The code looks for the cell with the value "Mechanics:" and then I
want it to assign the groups of mechanics names in the changing range
of names directly below the found cell to an array.

I have the following code snippet which I am having troubles with:

With Worksheets("Index")
Set c = .Cells.Find("Mechanics:", LookIn:=xlValues)
If Not c Is Nothing Then
MechNameStart = c.Offset(1, 0)
MechNameEnd = MechNameStart.End(xlDown)
End If
End With

I am getting Run-time error '424': Object required.
What I would like to do then is assign the entire range from the start
(MechNameStart) to the end (MechNameEnd) to an array.

Thanks in advance for any assistance...

Assing a dynamic range to an array
 

Bob missed a couple of typos in your original code (missing "Set" statements).

Option Explicit
Sub testme()

Dim c As Range
Dim MechNameStart As Range
Dim MechNameEnd As Range
Dim myArray As Variant

With Worksheets("Index")
Set c = .Cells.Find("Mechanics:", LookIn:=xlValues)
If Not c Is Nothing Then
Set MechNameStart = c.Offset(1, 0)
Set MechNameEnd = MechNameStart.End(xlDown)
End If
myArray = Application.Transpose(MechNameStart _
.Resize(MechNameEnd.Row - MechNameStart.Row + 1))
End With
End Sub

And I bet you wanted to start with MechNameStart.

Bob used application.transpose() to make the array a one dimensional array.

If Bob had used:
myArray = MechNameStart.Resize(MechNameEnd.Row - MechNameStart.Row + 1)

Then myArray would have had two dimensions (x Rows by 1 column).

Bob Phillips wrote:

With Worksheets("Index")
Set c = .Cells.Find("Mechanics:", LookIn:=xlValues)
If Not c Is Nothing Then
MechNameStart = c.Offset(1, 0)
MechNameEnd = MechNameStart.End(xlDown)
End If
MyArray = Application.Transpose(c.Resize(MechNameEnd.Row -
MechNameStart + 1))
End With

--
__________________________________
HTH

Bob

wrote in message
...
I have a sheet populated with a changing list of names. I am trying to
create code which will assign those names to a dynamic array.
The code looks for the cell with the value "Mechanics:" and then I
want it to assign the groups of mechanics names in the changing range
of names directly below the found cell to an array.

I have the following code snippet which I am having troubles with:

With Worksheets("Index")
Set c = .Cells.Find("Mechanics:", LookIn:=xlValues)
If Not c Is Nothing Then
MechNameStart = c.Offset(1, 0)
MechNameEnd = MechNameStart.End(xlDown)
End If
End With

I am getting Run-time error '424': Object required.
What I would like to do then is assign the entire range from the start
(MechNameStart) to the end (MechNameEnd) to an array.

Thanks in advance for any assistance...

--

Dave Peterson