How to "return" the array element number in VBA
 

2003

Challenge:

For Each myCell in myRange
.....
.....
'Would like to be able to move to the next myCell in a (non-contiguous)
'myRange if the current myCell.Value = 0. I do not know how to return
'the current myCell array element number AND to increment to the next
'non-zero "0" myCell.value
.....
.......
Next myCell

How to "return" the array element number in VBA
 

I think you are going to have a problem here... You are in a "For Each"
statement which by it's very nature requires you to go through each and every
element... No skipping allowed...
--
HTH...

Jim Thomlinson


"EagleOne" wrote:

2003

Challenge:

For Each myCell in myRange
.....
.....
'Would like to be able to move to the next myCell in a (non-contiguous)
'myRange if the current myCell.Value = 0. I do not know how to return
'the current myCell array element number AND to increment to the next
'non-zero "0" myCell.value
.....
.......
Next myCell

How to "return" the array element number in VBA
 

Maybe you don't have to move at all. Maybe you can just do the work you want on
the non-zero values.

dim myCell as range
dim myRange as range

'set myRange = somerangehere

for each mycell in myrange.cells
if mycell.value = 0 then
'do nothing
else
mycell.value = mycell.value + 1 'whatever increment
end if
next mycell



EagleOne wrote:

2003

Challenge:

For Each myCell in myRange
....
....
'Would like to be able to move to the next myCell in a (non-contiguous)
'myRange if the current myCell.Value = 0. I do not know how to return
'the current myCell array element number AND to increment to the next
'non-zero "0" myCell.value
....
......
Next myCell

--

Dave Peterson

How to "return" the array element number in VBA
 

No wonder I could not find out how to skip!

Thanks Jim


Jim Thomlinson wrote:
I think you are going to have a problem here... You are in a "For Each"
statement which by it's very nature requires you to go through each and every
element... No skipping allowed...
--
HTH...

Jim Thomlinson


"EagleOne" wrote:

2003

Challenge:

For Each myCell in myRange
.....
.....
'Would like to be able to move to the next myCell in a (non-contiguous)
'myRange if the current myCell.Value = 0. I do not know how to return
'the current myCell array element number AND to increment to the next
'non-zero "0" myCell.value
.....
.......
Next myCell

How to "return" the array element number in VBA
 

Thanks Dave. It has been a tough week. I did not see the forest for
the trees.

BTW, your guidance this week or so, is/was greatly appreciated

Dennis

Dave Peterson wrote:
Maybe you don't have to move at all. Maybe you can just do the work you want on
the non-zero values.

dim myCell as range
dim myRange as range

'set myRange = somerangehere

for each mycell in myrange.cells
if mycell.value = 0 then
'do nothing
else
mycell.value = mycell.value + 1 'whatever increment
end if
next mycell



EagleOne wrote:

2003

Challenge:

For Each myCell in myRange
....
....
'Would like to be able to move to the next myCell in a (non-contiguous)
'myRange if the current myCell.Value = 0. I do not know how to return
'the current myCell array element number AND to increment to the next
'non-zero "0" myCell.value
....
......
Next myCell

--

Dave Peterson