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.

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

--
Message posted from http://www.ExcelForum.com

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.

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


"SuperJas" wrote in message
...
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!)
i=i+1

Next DummyCell

End Sub
------------------------------------------

Could you please help?

Thankyou very much,

SuperJas.

Fantastic! Thanks Bernie! =

SuperJas.