Solved: Having Trouble with For Each Loop

[Opv]

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:

[VBA]
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
[/VBA]

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]

[vba]

For i = LBound(myNames) To UBound(myNames)

MsgBox myNames(i)
Next i
[/vba]

as an example

[Opv]

[vba]

For i = LBound(myNames) To UBound(myNames)

MsgBox myNames(i)
Next i
[/vba]
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]

Show us your code. It will make it simpler.

[Opv]

Show us your code. It will make it simpler.

Such as it is. LOL (I am always reluctant to show my ignorance.)

[VBA]
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
[/VBA]

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

[SamT]

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
[vba]
Worksheets("Transactions").Activate

With Worksheets

[/vba]
with
[vba]
With Worksheets("Transactions")
yaddah
yaddah
End With
[/vba]

Next:
I'm not real familiar with "Transpose" and arrays, but what happens if you replace
[vba]
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
[/vba]
with
[vba]
Range("B140:B143").Value = Application.Transpose(myNames)
[/vba]

Last:
If you put Option Base 1 at the top of your code module then
[vba]
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
[/vba]
should work with any number of names in your array.

With Option Base 0 use
[vba]
.Offset(i, 0).Value = etc
[/vba]

[Opv]

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


[vba]
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
[/vba]

[Opv]

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]

[vba]

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
[/vba]

[Opv]

[vba]

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
[/vba]

Thanks. This exposes me to yet additional terms I've not run into yet. Very succinct...nice.

[SamT]

If you're satisfied, can you mark this thread Solved, using the Thread Tools at the top?

Thanks.

[Opv]

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]

Yes, reusing the counter variable is ok. You should reset it for other scenarios though.

[Opv]

Thanks. I'll be sure to do that.