View Full Version : Solved: Having Trouble with For Each Loop
I have a function that generates a unique array of names from a predefined range of cells. I am trying to to create a loop to extract those names so that I can use them in another function. I don't know why I have such a problem grasping how loops work. I understand on an elementary level what they are suppose to do but tend to get lost in all the different types and varying requirements for how they should be constructed.
At any rate, here are the variables used:
Dim uNames As Variant 'Number of unique names in the Array
Dim myNames As Variant 'The actual array of names
uNames = UniqueItems(True) 'Captures number of unique names
myNames = UniqueItems(False) 'Captures the array of names
How would I set up a For Each loop (if that is indeed the appropriate type of loop) to extract the individual names from myNames so that I could reference them individually and make them available individually to other functions?
Thanks,
Opv
Bob Phillips
03-25-2010, 07:59 AM
For i = LBound(myNames) To UBound(myNames)
MsgBox myNames(i)
Next i
as an example
For i = LBound(myNames) To UBound(myNames)
MsgBox myNames(i)
Next i
as an example
Thanks. I am able using this to extract the names. For some reason, when I insert the names into a range of cells, something is forcing me to put then in a horizontal array in order for all of the names to be displayed. If I insert them in a vertical range OR I use Application.Transpose function, the right number of names populate the cells but all of the names are the same. Any idea what might be causing that?
Bob Phillips
03-25-2010, 10:52 AM
Show us your code. It will make it simpler.
Show us your code. It will make it simpler.
Such as it is. LOL (I am always reluctant to show my ignorance.)
Sub getUniqueNames()
Dim uNames As Variant
Dim myNames As Variant
Dim myRange As Variant
Dim myCell As Variant
Worksheets("Transactions").Activate
With Worksheets
uNames = UniqueItems(True) 'Number of unique names in Array
myNames = UniqueItems(False) 'Array of names generated by the UniqueItems() function
For n = 1 To LBound(myNames)
ActiveCell.Select
For i = LBound(myNames) To UBound(myNames)
Range("B140:B143").Value = Application.Transpose(myNames(i))
Next i
Next n
'Range("B140").Value = myNames(1)
'Range("B141").Value = myNames(2)
'Range("B142").Value = myNames(3)
End With
This code populates the cells in the range, but it puts the same name in all the cells. If I omit the Transpose and change the range to Range("B140:E140"), it puts the individual names in the cells. I need them to be displayed vertically.
On a related note, it would be great if there was a way to dynamically create the range based on the number of names in the myNames array.
Thanks,
Opv
That For n=1 to LBound loop is specious because Lbound of an array is either 0 or 1, depending if you have Option Base 1 at the top of your code page. Take it out of your code.
First:
Replace
Worksheets("Transactions").Activate
With Worksheets
with
With Worksheets("Transactions")
yaddah
yaddah
End With
Next:
I'm not real familiar with "Transpose" and arrays, but what happens if you replace
For n = 1 To LBound(myNames)
ActiveCell.Select
For i = LBound(myNames) To UBound(myNames)
Range("B140:B143").Value = Application.Transpose(myNames(i))
Next i
Next n
with
Range("B140:B143").Value = Application.Transpose(myNames)
Last:
If you put Option Base 1 at the top of your code module then
With Worksheets("Transactions")
yaddah
yaddah
With Range("B140")
For i = 1 to UBound(myNames)
.Offset(i -1,0).Value = mynames(i)
Next i
End With
End With
should work with any number of names in your array.
With Option Base 0 use
.Offset(i, 0).Value = etc
I revised my code as follows and it seems to be doing what I'm needing it to do. Now, as to whether there is a better, more efficient way to do it remains outstanding.
Thanks,
Opv
Sub getUniqueNames()
Dim myRange As Variant
Worksheets("Transactions").Activate
With Worksheets("Transactions")
myNames = UniqueItems(False) 'Array of names generated by the UniqueItems() function
Range("B140").Select
For i = LBound(myNames) To UBound(myNames)
ActiveCell.Value = myNames(i)
ActiveCell.Offset(1, 0).Select
Next i
End With
End Sub
Sam, it appears I posted my updated code about the same time you posted your response. I realized some of the flaws you mentioned after I posted my original code.
Thanks,
Opv
P.S. I have updated the latest posted code to include "With Worksheets("Transactions") as suggested.
Bob Phillips
03-25-2010, 03:19 PM
Sub getUniqueNames()
Dim myNames As Variant
myNames = UniqueItems(False) 'Array of names generated by the UniqueItems() function
Worksheets("Transactions").Range("B140").Resize(UBound(myNames) - LBound(myNames) + 1) = Application.Transpose(myNames)
End Sub
Sub getUniqueNames()
Dim myNames As Variant
myNames = UniqueItems(False) 'Array of names generated by the UniqueItems() function
Worksheets("Transactions").Range("B140").Resize(UBound(myNames) - LBound(myNames) + 1) = Application.Transpose(myNames)
End Sub
Thanks. This exposes me to yet additional terms I've not run into yet. Very succinct...nice.
If you're satisfied, can you mark this thread Solved, using the Thread Tools at the top?
Thanks.
I've played around with several types of loops since my original post. I've been able to get the For Next, Do While and Do Until loops to work. I still can't grasp when and how Wend is appropriate and how it differs from While.
With respect to For Next loops, is it acceptable to reuse the same long variable within the same sub (after the previous loop has completed) to in effect create two or three different loops, or does that cause actual or potential problems with the sub?
Kenneth Hobs
05-08-2011, 03:52 PM
Yes, reusing the counter variable is ok. You should reset it for other scenarios though.
Thanks. I'll be sure to do that.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.