Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
row number question in an array | Excel Worksheet Functions | |||
How do I set up a 6 day work week in Excel? | Excel Worksheet Functions | |||
number anagram | Excel Worksheet Functions | |||
Permutations of an array element < to a value | Excel Worksheet Functions | |||
Array Formulas take waaaay too long... | Excel Worksheet Functions |