VBA - Find and Add cells to dynamic array

[neodjandre]

I have column C with the following structure:

[blank]
item1.1
item1.2
item1.3
BA1
[blank]
item2.1
item2.2
BA2
[blank]
item3.1
item3.2
item3.3
BA3
[blank]
and so on...

I am looking for a macro that finds let's say "BA2" and then adds all items above "BA2" (until a blank cell is reached) to a dynamic array. In this example the array will have item2.1 and item2.2

Not sure how to go about this, any help would be much appreciated.
Andrew

 

[MickG]

With the value to find in cell "B1", try this for result in column "C".

[COLOR="Navy"]Sub[/COLOR] MG22May27
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("A:A").SpecialCells(xlCellTypeConstants).Areas
    [COLOR="Navy"]If[/COLOR] Dn(Dn.Count) = [B1] [COLOR="Navy"]Then[/COLOR] Ray = Application.Transpose(Dn)
[COLOR="Navy"]Next[/COLOR] Dn
'[COLOR="Green"][B]REMOVE LINE BELOW IF ARRAY lIST NOT REQUIRED[/B][/COLOR]
[COLOR="Navy"]If[/COLOR] IsArray(Ray) [COLOR="Navy"]Then[/COLOR]
    Range("C1").Resize(UBound(Ray)) = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

Regards Mick

 

[neodjandre]

With the value to find in cell "B1", try this for result in column "C".

[COLOR="Navy"]Sub[/COLOR] MG22May27
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("A:A").SpecialCells(xlCellTypeConstants).Areas
    [COLOR="Navy"]If[/COLOR] Dn(Dn.Count) = [B1] [COLOR="Navy"]Then[/COLOR] Ray = Application.Transpose(Dn)
[COLOR="Navy"]Next[/COLOR] Dn
'[COLOR="Green"][B]REMOVE LINE BELOW IF ARRAY lIST NOT REQUIRED[/B][/COLOR]
[COLOR="Navy"]If[/COLOR] IsArray(Ray) [COLOR="Navy"]Then[/COLOR]
    Range("C1").Resize(UBound(Ray)) = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

Regards Mick

thanks for your help Mick, BA01, BA02, BA03 are formula cells. So as the items above them.
Also why are you referring to columns A:A in your solution?

 

[MickG]

Try changing that line to the below:-

For Each Dn In Range("A:A").SpecialCells(xlCellTypeFormulas).Areas

 

[MickG]

Sorry missed column "C" , Try this for Value = "B1" and results in column "D".

[COLOR="Navy"]Sub[/COLOR] MG22May06
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Ray [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Range("C:C").SpecialCells(xlCellTypeFormulas).Areas
[COLOR="Navy"]If[/COLOR] Dn(Dn.Count) = [B1] [COLOR="Navy"]Then[/COLOR] Ray = Application.Transpose(Dn)
[COLOR="Navy"]Next[/COLOR] Dn
'[COLOR="Green"][B]REMOVE LINE BELOW IF ARRAY lIST NOT REQUIRED[/B][/COLOR]
[COLOR="Navy"]If[/COLOR] IsArray(Ray) [COLOR="Navy"]Then[/COLOR]
    Range("D:D").ClearContents
    Range("D1").Resize(UBound(Ray)) = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]

Regards Mick