More fun with arrays

[mountainman88]

I would like an array that takes this table (i will call it TableA):

[TABLE="width: 406"]
<tbody>[TR]
[TD]GrandTotal[/TD]
[TD]Total1[/TD]
[TD]Total2[/TD]
[TD](empty cell)[/TD]
[TD]Total3[/TD]
[/TR]
[TR]
[TD]Total1[/TD]
[TD]Apples[/TD]
[TD]Beatles[/TD]
[TD][/TD]
[TD]Cats[/TD]
[/TR]
[TR]
[TD]Total2[/TD]
[TD]Bananas[/TD]
[TD]Stones[/TD]
[TD][/TD]
[TD]Who[/TD]
[/TR]
[TR]
[TD]Total3[/TD]
[TD]Cherries[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 331"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

(would ideally like a msgbox to ask the sheetname and first cell of where TableA is located)

and turns TableA into a 2d array called 'TableA' where:

Row 1 of the array is the TableA contents sorted as follows:

[TABLE="width: 663"]
<tbody>[TR]
[TD="class: xl65, width: 76"]GrandTotal[/TD]
[TD="class: xl65, width: 51"]Total1[/TD]
[TD="width: 47"]Apples[/TD]
[TD="width: 57"]Bananas[/TD]
[TD="width: 57"]Cherries[/TD]
[TD="class: xl65, width: 51"]Total2[/TD]
[TD="width: 51"]Beatles[/TD]
[TD="width: 51"]Stones[/TD]
[TD="class: xl65, width: 51"](empty)[/TD]
[TD="class: xl65, width: 51"]Total3[/TD]
[TD="width: 51"]Cats[/TD]
[TD="width: 69"]Who[/TD]
[/TR]
</tbody>[/TABLE]

(Notice any items already in the top row of TableA are ignored and not duplicated when in the format above)

and

Row 2 of the array contains:

- 'total' if the row 1 array item is on the top of TableA eg. 'GrandTotal, Total1 etc'
- 'data' if the row 1 array item is NOT on the top row of TableA
- 'blank' if the row 1 array item is empty (ie left a blank column in TableA) like between stones and Total3

I would like the array to exclude any other 'blanks' eg under Stones or Who. Just include the 'blanks' in the top row of TableA..

The array should look like this:

[TABLE="width: 663"]
<tbody>[TR]
[TD]GrandTotal[/TD]
[TD]Total1[/TD]
[TD]Apples[/TD]
[TD]Bananas[/TD]
[TD]Cherries[/TD]
[TD]Total2[/TD]
[TD]Beatles[/TD]
[TD]Stones[/TD]
[TD](empty)[/TD]
[TD]Total3[/TD]
[TD]Cats[/TD]
[TD]Who[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]Total[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Total[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Blank[/TD]
[TD]Total[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
</tbody>[/TABLE]

hope this makes sense, should test you guys ha

 

[mountainman88]

I should mention that TableA is NOT a table object, its just a range of normal excel data starting in the cell and sheet given through the message box request.

 

[MickG]

Try this:-
Results on sheet2.

[COLOR="Navy"]Sub[/COLOR] MG24Jun12
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, temp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray() [COLOR="Navy"]As[/COLOR] Variant, Loc [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Sp [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] R [COLOR="Navy"]As[/COLOR] Range
Loc = InputBox("Enter Sheet name, ""comma"" and cell address" & vbLf & "Exp:- Sheet1,A10", "Sheet and Address", "Sheet name ""Comma"" and cell Address")
Sp = Split(Loc, ",")
[COLOR="Navy"]Set[/COLOR] R = Range(Sp(1))
[COLOR="Navy"]With[/COLOR] Sheets(Sp(0))
    [COLOR="Navy"]Set[/COLOR] Rng = .Range(R.Address, Cells(R.Row, Columns.Count).End(xlToLeft))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
   [COLOR="Navy"]If[/COLOR] Not .exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
     .Add Dn.Value, ""
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Sheets(Sp(0))
[COLOR="Navy"]Set[/COLOR] Rng = .Range(.Cells(R.Row, R.Column), .Cells(Rows.Count, R.Column).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
  
    [COLOR="Navy"]For[/COLOR] Ac = 0 To 4
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
            [COLOR="Navy"]If[/COLOR] Dn.Row > R.Row And Not .exists(Dn.Offset(, Ac).Value) [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                ReDim Preserve Ray(1 To 2, 1 To c)
               Ray(1, c) = Dn.Offset(, Ac).Value
            [COLOR="Navy"]ElseIf[/COLOR] Dn.Row = R.Row [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                ReDim Preserve Ray(1 To 2, 1 To c)
                Ray(1, c) = Dn.Offset(, Ac).Value
            [COLOR="Navy"]End[/COLOR] If
         [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]Next[/COLOR] Ac

[COLOR="Navy"]For[/COLOR] Ac = 1 To UBound(Ray, 2)
    [COLOR="Navy"]If[/COLOR] .exists(Ray(1, Ac)) And Not IsEmpty(Ray(1, Ac)) [COLOR="Navy"]Then[/COLOR]
          Ray(2, Ac) = "Total"
    [COLOR="Navy"]ElseIf[/COLOR] Not .exists(Ray(1, Ac)) [COLOR="Navy"]Then[/COLOR]
        Ray(2, Ac) = "Data"
    [COLOR="Navy"]ElseIf[/COLOR] IsEmpty(Ray(1, Ac)) [COLOR="Navy"]Then[/COLOR]
       Ray(2, Ac) = "Blank"
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Ac

 Sheets("Sheet2").Range("A1").Resize(2, UBound(Ray, 2)).Value = Ray
[COLOR="Navy"]End[/COLOR] With



[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mountainman88]

Hey thanks, can’t wait to try it out later!