VBA - How to define and assign values in Array

[FKalinx]

Hi,

I am trying to do some analysis on an array in Excel and want to use VBA.

As a first step I need to define a range or array (not sure) that includes the 28 cells below. Then I need to pull out certain characteristics (using Offset, UCase) and display these in a table. I will probably need help on this but as a first step how do I define my array?

Dim aRng As Range
Dim aCell As Range
Set aRng = Selected

I've tried Set aRng = Range ("A1:A28") but this doesn't work.

Please help!

______

[TABLE="width: 103"]
<colgroup><col></colgroup><tbody>[TR]
[TD]UK[/TD]
[/TR]
[TR]
[TD]Brand Star[/TD]
[/TR]
[TR]
[TD]Total Cinemas 109[/TD]
[/TR]
[TR]
[TD]Total Screens 868[/TD]
[/TR]
[TR]
[TD]ITALY[/TD]
[/TR]
[TR]
[TD]Brand Holy[/TD]
[/TR]
[TR]
[TD]Total Cinemas 46[/TD]
[/TR]
[TR]
[TD]Total Screens 466[/TD]
[/TR]
[TR]
[TD]IRELAND[/TD]
[/TR]
[TR]
[TD]Brand Bird[/TD]
[/TR]
[TR]
[TD]Total Cinemas 10[/TD]
[/TR]
[TR]
[TD]Total Screens 68[/TD]
[/TR]
[TR]
[TD]PORTUGAL[/TD]
[/TR]
[TR]
[TD]Brand Holy[/TD]
[/TR]
[TR]
[TD]Total Cinemas 3[/TD]
[/TR]
[TR]
[TD]Total Screens 45[/TD]
[/TR]
[TR]
[TD]SPAIN[/TD]
[/TR]
[TR]
[TD]Brand Cinesa[/TD]
[/TR]
[TR]
[TD]Total Cinemas 46[/TD]
[/TR]
[TR]
[TD]Total Screens 535[/TD]
[/TR]
[TR]
[TD]GERMANY[/TD]
[/TR]
[TR]
[TD]Brand Kinowelt[/TD]
[/TR]
[TR]
[TD]Total Cinemas 23[/TD]
[/TR]
[TR]
[TD]Total Screens 203[/TD]
[/TR]
[TR]
[TD]AUSTRIA[/TD]
[/TR]
[TR]
[TD]Brand Kinowelt[/TD]
[/TR]
[TR]
[TD]Total Cinemas 3[/TD]
[/TR]
[TR]
[TD]Total Screens 38[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

You're getting Arrays and Range Objects mixed Up!!!
Have a look at these two codes, One a Range Object one an Array.

Private [COLOR=Navy]Sub[/COLOR] CommandButton1_Click()
'[COLOR=Green][B]This is a "For Each loop" through an range[/B][/COLOR]
[COLOR=Navy]Dim[/COLOR] R [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Data [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] aCell [COLOR=Navy]As[/COLOR] Range
Set Data = Range("B2:B29") '[COLOR=Green][B]Assign the values to a Range Object[/B][/COLOR]
    [COLOR=Navy]If[/COLOR] aCell.Value = UCase(aCell.Value) [COLOR=Navy]Then[/COLOR]
        aCell.Value = "Uppercase"
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] aCell
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]


Private [COLOR=Navy]Sub[/COLOR] CommandButton2_Click()
'[COLOR=Green][B]This is a "For Each" loop through a Array[/B][/COLOR]
Dim R As Long '[COLOR=Green][B]Variable to index rows in array[/B][/COLOR]
Dim Data As Variant '[COLOR=Green][B]Variable to house the values from the range[/B][/COLOR]
[COLOR=Navy]Dim[/COLOR] aCell [COLOR=Navy]As[/COLOR] Variant
Data = Range("B2:B29") '[COLOR=Green][B]Assign the values to a two-dimensional array[/B][/COLOR]
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] aCell [COLOR=Navy]In[/COLOR] Data
    [COLOR=Navy]If[/COLOR] aCell = UCase(aCell) [COLOR=Navy]Then[/COLOR]
        aCell = "Uppercase"
        MsgBox aCell
        '[COLOR=Green][B]NB:- The cell will not read "Uppercase" the value in the array will!!![/B][/COLOR]
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] aCell
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[FKalinx]

You're getting Arrays and Range Objects mixed Up!!!
Have a look at these two codes, One a Range Object one an Array.

Private [COLOR=Navy]Sub[/COLOR] CommandButton1_Click()
'[COLOR=Green][B]This is a "For Each loop" through an range[/B][/COLOR]
[COLOR=Navy]Dim[/COLOR] R [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Data [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] aCell [COLOR=Navy]As[/COLOR] Range
Set Data = Range("B2:B29") '[COLOR=Green][B]Assign the values to a Range Object[/B][/COLOR]
    [COLOR=Navy]If[/COLOR] aCell.Value = UCase(aCell.Value) [COLOR=Navy]Then[/COLOR]
        aCell.Value = "Uppercase"
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] aCell
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]


Private [COLOR=Navy]Sub[/COLOR] CommandButton2_Click()
'[COLOR=Green][B]This is a "For Each" loop through a Array[/B][/COLOR]
Dim R As Long '[COLOR=Green][B]Variable to index rows in array[/B][/COLOR]
Dim Data As Variant '[COLOR=Green][B]Variable to house the values from the range[/B][/COLOR]
[COLOR=Navy]Dim[/COLOR] aCell [COLOR=Navy]As[/COLOR] Variant
Data = Range("B2:B29") '[COLOR=Green][B]Assign the values to a two-dimensional array[/B][/COLOR]
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] aCell [COLOR=Navy]In[/COLOR] Data
    [COLOR=Navy]If[/COLOR] aCell = UCase(aCell) [COLOR=Navy]Then[/COLOR]
        aCell = "Uppercase"
        MsgBox aCell
        '[COLOR=Green][B]NB:- The cell will not read "Uppercase" the value in the array will!!![/B][/COLOR]
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] aCell
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

Thanks Mick.