Assing a dynamic range to an array

[wolfie115]

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

 

[wolfie115]

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

 

[Bob Phillips]

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

 

[Dave Peterson]

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