For...Each Loop Problem

[Guest]

Hi

I am trying to loop my code for each cell ("DummyCell") within a defined region ("MyRegion"), which is column F for a current region (please see code below). However, instead of looping through each cell, it just assigns DummyCell to the MyRegion

Here is my code

-------------------------------------
Sub LoopTest(

Dim DummyCell as Range, MyRegion as Rang
Dim Value() as Strin
Dim i as Byt

i=

Set MyRegion = Cells(1,1).CurrentRegion.Columns(6

For Each DummyCell in MyRegio

Redim Preserve Value(i
Value(i)=DummyCell.Value <<< Error Here (since it's trying to set it to the value of MyRegion!
i=i+

Next DummyCel

End Su
-----------------------------------------

Could you please help

Thankyou very much

SuperJas.

 

[Kieran]

SuperJas try


Sub LoopTest()

Dim DummyCell as Range
Dim MyRegion as Range
Dim Value() as String
Dim i as Integer

i=1

Set MyRegion = Cells(1,1).CurrentRegion.Columns(6)
Redim Value(myregion.cells.count)
For Each DummyCell in MyRegion

Value(i)=DummyCell.Value
i=i+1

Next DummyCell

End Su

 

[Bernie Deitrick]

SupeJas,

Add a

..Cells

to

Set MyRegion = Cells(1, 1).CurrentRegion.Columns(6)

to get

Set MyRegion = Cells(1, 1).CurrentRegion.Columns(6).Cells

As written, you are setting MyRegion to a column, and trying to loop through
the columns of column 6 - not the cells.

HTH,
Bernie
MS Excel MVP

Hi,

I am trying to loop my code for each cell ("DummyCell") within a defined

region ("MyRegion"), which is column F for a current region (please see code
below). However, instead of looping through each cell, it just assigns
DummyCell to the MyRegion!

Here is my code:

--------------------------------------
Sub LoopTest()

Dim DummyCell as Range, MyRegion as Range
Dim Value() as String
Dim i as Byte

i=1

Set MyRegion = Cells(1,1).CurrentRegion.Columns(6)

For Each DummyCell in MyRegion

Redim Preserve Value(i)
Value(i)=DummyCell.Value <<< Error Here (since it's trying

to set it to the value of MyRegion!)

 

[Guest]

Hi Kieran

Thanks for your help. Unfortunately excel is still setting DummyCell to MyRegion when I did a simple DummyCell.Select test. It's very odd isn't it?

Thanks

SuperJas.

 

[Guest]

Fantastic! Thanks Bernie! =

SuperJas.