Cell range as an array

decafme

New Member
Joined
Feb 18, 2004
Messages
3
I have a range of cells that hold headers for each column. Within this range, I want to be able to select a set of cells and add them to an array. The problem, as ever, is that each time this is used, the number of cells in the subset could be different. Here is what is always true though:

1. The subset of cells always begins on cell F1.
2. The cell after the last cell in the subset, always states "is_success" in it.

All the cells in between these two points are what I want to pass to the array. It is not the contents of the cell that I want to pass, but the cell references (F1, for example).

Any suggestions on this would be gratefully received.

Thanks folks
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Are you talking a formula or VBA? If its VBA you will have to set a range based on your criteria of locating the cell with the value "is_success" and then loop through this range adding the address as the value to the array.

Heres an example that puts the result in cells A3 downwards.

Code:
Sub AddressArray()
Dim MyArray(), c As Variant, i As Integer, Rng As Range
Dim FindMe As Variant

Set FindMe = [1:1].Find("is_success", LookIn:=xlValues)

'Set range if text is found otherwise stop procedure
If Not FindMe Is Nothing Then
    Set Rng = Range(Cells(1, 6), Cells(1, FindMe.Column))
Else
    MsgBox "The text 'is_success' cannot be found. No array created"
    Exit Sub
End If

'resize array & set counter
ReDim MyArray(1 To Rng.Cells.Count)
i = 1

'Add address values to the array
For Each c In Rng
MyArray(i) = c.Address 'or c.Address(false,false) for non-absolute refs
i = i + 1
Next c

'place array into cells A3 downwards
Range("A3:A" & Rng.Cells.Count + 2) = Application.WorksheetFunction.Transpose(MyArray)

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top