Loops

[elgringo56]

Not sure I can explain this, but will try. I have a list that may be 500 rows long, but does not need to be. within this list I have many duplicate entrys. I use a loop, that I go thru 500 times to insure that I examin all possible entrys. However, if I only have 81 different items in the list, I would like to only loop 81 times. I do have a cell that will tell me how many items are in the list. right now, my loop starts with For i = 1 to 500. I would like the 500 part of that statement to equal the number that I have in the cell that tells me how many items are to be examined. is this possible?

[hennahairgel]

yeah, just put in a string saying
totalnumberofposs = Range("A1").Value

then change your for statement to

For i = 1 to totalnumberofposs

[elgringo56]

That looks pretty simple, I will give it a try. Thank you for responding

[Jay Petrulis]

Hi,

How are you going to count the number of unique entries that will be placed in A1?

You can use Aladin's excellent array formula

{=SUM(IF(LEN(range),1/COUNTIF(range,range)))}

or you can also do this at runtime,

Sub test()
Dim rng As String, x As Long, UniqueItems As Long

rng = Intersect(ActiveSheet.UsedRange, Range("A:A")).Address(False, False)
x = Evaluate("=SUM(IF(LEN(" & rng & "),1/COUNTIF(" & rng & "," & rng & ")))")

For x = 1 To UniqueItems
' your code
Next x

End Sub

Bye,
Jay