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...

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...

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...

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