Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For each
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For each
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 -- jb "JMay" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For each
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 "JMay" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For each
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. JMay wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For each
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 "john" wrote: 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 -- jb "JMay" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
For each
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 "Bernard Liengme" wrote: 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 "JMay" wrote in message ... 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
For each
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 -- Rick (MVP - Excel) "JMay" wrote in message ... 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 "john" wrote: 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 -- jb "JMay" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|