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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

parry

MrExcel MVP
Joined
Aug 20, 2002
Messages
3,355
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
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,565
Messages
5,765,147
Members
425,263
Latest member
alcat

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
Top