Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non-Sequential Count
I know normally to do a count one would use
For count = 1 To 4 Next count But what if I don't want to do 1-4, and I wanted to do like 1,2,5-8? What would I use then? -- Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non-Sequential Count
a couple of ways
Sub test() Dim i As Long For i = 1 To 8 If i < 3 Or i 4 Then MsgBox i End If Next End Sub Sub test2() Dim i As Long Dim arr As Variant arr = Array(1, 2, 5, 6, 7, 8) For i = LBound(arr) To UBound(arr) MsgBox arr(i) Next End Sub -- Gary "Brian" wrote in message ... I know normally to do a count one would use For count = 1 To 4 Next count But what if I don't want to do 1-4, and I wanted to do like 1,2,5-8? What would I use then? -- Brian |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non-Sequential Count
num = array(1,2,5,7,8)
For each count in num "Brian" wrote: I know normally to do a count one would use For count = 1 To 4 Next count But what if I don't want to do 1-4, and I wanted to do like 1,2,5-8? What would I use then? -- Brian |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non-Sequential Count
Joel thanks for the help over the past few days. This worked fine when I
explicitly stated the numbers; however, when I do something like the following I get Subscript Out of Range error: Dim strABC as String, lResult as Long strABC = "1,2,5,6,7,8" num=array(strABC) For each count in num lResult = lResult + Worksheets(count).Range("A1").Value Next count MsgBox lResult I'm using criteria to build the string strABC, so this is just an example, and I can't explicitly say what numbers I want. Thanks again. -- Brian "Joel" wrote: num = array(1,2,5,7,8) For each count in num "Brian" wrote: I know normally to do a count one would use For count = 1 To 4 Next count But what if I don't want to do 1-4, and I wanted to do like 1,2,5-8? What would I use then? -- Brian |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Non-Sequential Count
you array is one element "1,2,5,6,7,8"
instead of separate elements if you're doing it like that, you'd have to use something like the split function to separate the values split(num(0),",") try this with the code you just posted: add this line Dim num As Variant comment out the for next loop lines then click debug, add a watch and type in num create a breakpoint on the message box line so the code stops click the + sign next to num in the watch window. you have one element once you've seen it, stop the code now, do the same procedure with the code i posted below, you will have 6 elements you're better off doing something like this Sub test() Dim i As Long, lResult As Long Dim num As Variant num = Array(1, 2, 5, 6, 7, 8) For i = LBound(num) To UBound(num) lResult = lResult + Worksheets(num(i)).Range("A1").Value Debug.Print lResult Next MsgBox lResult End Sub -- Gary "Brian" wrote in message ... Joel thanks for the help over the past few days. This worked fine when I explicitly stated the numbers; however, when I do something like the following I get Subscript Out of Range error: Dim strABC as String, lResult as Long strABC = "1,2,5,6,7,8" num=array(strABC) For each count in num lResult = lResult + Worksheets(count).Range("A1").Value Next count MsgBox lResult I'm using criteria to build the string strABC, so this is just an example, and I can't explicitly say what numbers I want. Thanks again. -- Brian "Joel" wrote: num = array(1,2,5,7,8) For each count in num "Brian" wrote: I know normally to do a count one would use For count = 1 To 4 Next count But what if I don't want to do 1-4, and I wanted to do like 1,2,5-8? What would I use then? -- Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Criteria & Return Sequential Count | Excel Worksheet Functions | |||
Formula to count number of sequential days in a row | Excel Worksheet Functions | |||
Sequential dates. | Excel Discussion (Misc queries) | |||
Non-sequential VLOOKUP function -OR- sequential sort of web query | Excel Worksheet Functions | |||
Sequential names on Sequential pages | Excel Worksheet Functions |