For each

  • Thread starter Thread starter JMay
  • Start date Start date
J

JMay

I've assigned a Obj variable: Set oRng=C3:P84 (there are 84 elements/items)
So I then have

Dim c as Range
'code
For each c in oRng

Next c

As I step thru the code I need to perform an action when (using IF)
c reaches element, say 32 -- How is this done using the For each method?
I know I could assign a counter i (and do the i = i + 1 inside), but is that
necessary using the For each.

Thanks in advance..

Jim
 
see if this helps you

Sub aaa()
Dim c As Range
Dim oRng As Range

'code

Set oRng = Range("C3:P84")

For Each c In oRng

If c.Value = 32 Then MsgBox c.Address

Next c

End Sub
 
Not sure how you get 84 elements in C3:P84
However, here is an alternative way to cycle thru all the elements (left to
right, row by row) if you do not want to use Each and a counter
Sub tryme()
Set oRng = Range("C3:P84")
For j = 1 To oRng.Count
If j = 32 then MsgBox oRng(j)
Next j
End Sub

best wishes
 
Does this mean you're looking for the value 32 in one of the cells?
Or does it mean you're looking at the 32nd cell in that range?

And if it's the 32nd cell in the range, how do you want to loop?
through rows, then columns (all of row 3 before going to row 4, ...)
or
through columns, then rows (all of column C, then column D, ...)
or
some random loop <vbg>.
 
Thanks John,,

Sorry I wasn't clear in stating the facts. My oRng (C3:P8) contains a
unique set of integers. In my For Each loop a Variable interger is supplied,
say 32. I need for my activecell to change to the cell address that is
oRng(32).

Jim
 
Thanks Bernard,
My intent was:
First, From Col C to Col P Number of Columns = 14
Next Row 3:8 - Number of Rows = 6 (14 X 6 = 84
So obvioulsy, I should have said: C3:P8 (Part of my current vertigo...,
Sorry)

So, back to the drawing board, using your suggestion INSTEAD of my orig idea;
Thanks,

Jim
 
Can you make use of something like this...

Set MyRange = Range("C3:P8")
OffsetIntoRange = 32
DesiredCellAddress = MyRange(OffsetIntoRange).Address
For Each C In MyRange
.....
.....
If C.Address = DesiredCellAddress Then
' You found the cell, so do whatever you want with it
End If
......
Next
 
Back
Top