What is a good way to create a variable size group that will loop in Excel 2003?
I have a procedure that runs on a large number of items, skipping certain items that do not meet a criterion. However, I then go back and run it for some people who missed the first pass. I am currently doing this by manually retraining the procedure for each individual person, but would ideally like the solution to be a little more hands on.
Something my boss suggested might be efficient would be to create a list (as in Data -> Lists) that contains the names of the elements in the question, and then iterate over the list. Unfortunately, my help file Foo seems to me to fail. - I don't know, I just don't know what to look for, or what
Running the Create Macro command shows that the VBA to create a list first goes through the lines ActiveSheet.ListObjects.Add (xlSrcRange, Range ("$ A $ 1"), xlYes) .Name = "List1"
Unfortunately, I cannot figure out how to make stuff with the resulting list. I'm looking to make a loop along the lines
For Each ListItem in List
Run the procedure on the text in ListItem.Value
Next ListItem
Any suggestions?
source to share
Perhaps something along these lines:
Dim Counter 'module level '
Sub RunSomeProc()
Counter = 0
'1st test '
SomeProc
'2nd Test skipped items'
For Each c In Range("c1:c" & Counter)
SomeProc
Next
End Sub
Sub SomeProc()
For Each c In Range("NamedRange1")
If SomeTest=SomeVal Then
'Write to 2nd test range '
Range("C1").Offset(Counter, 0) = c 'Value of cell'
Counter = Counter + 1
End If
Next
End Sub
source to share
You can iterate along this path:
set rgList = Range ("name_of_range")
For i = 1 To rgList.Rows.Count
'Do something using rgList.Cells (i, 1)
RunProcedure (rgList.Cells (i, 1))
Next i
I assumed the range is in a column; whether it was in a row, you had to iterate over the second index.
Of course, there may be better ways to iterate over a range; I am using this file on a small script and it works really well.
source to share