Looping through a cell range 1

[MatthewGB]

How do you loop through a cell range in Excel?
For example, i want the code to look through each cell in:-
Worksheets(1).Range("G7:G9&quot.

I have tried this:-
For Each r In Worksheets(1).Range("G7:G9&quot 'where r is dimmed as range

but it doesnt loop through the range.

Thanks

Matt

 

[tyhand]

hi matthewgb,

did you try doing a search on this forum for what you want to do? You can find many examples by doing a keyword search.

You do have the right idea (logically) - I just think your implementation and syntax is off.

Try the following:
First, declare your variables.

Dim R as Range
Dim c ' the c's data type is VARIANT by default

Now use the SET Statement to 'SET' the 'R' variable
to your desired target range.

Set R = ActiveSheet().Range("G7:G9&quot
' or in your case...
Set R = Worksheets(1).Range("G7:G9&quot

Now use the For Each... Next Statement to loop through your
'R' range. This is where the 'c' variable comes into play.

For Each c In R
' Do Whatever statements
Next

The 'c' is equivalent to each 'element' of the range.
It's the same as saying For Each 'element' In 'my range'.

For additional help, you can also use the VB help files in your excel or word application.

Hope this helps. Peace!
*****Thank You Have A Nice Day
~John 3:16

 

[MatthewGB]

Thanks tyhand. The prog now loops correctly through each cell in the range. Appreciated.