Setting A variable name From Loop

[ironhydroxide]

Hello,
I am trying to set a few variables within a loop, The loop function is for
the variables specifically.

I would like something to this effect

------------------------------------------------------------------------------------------------
ZoneNumber = 1
ZoneFromRow = 2
"Zone"& ZoneNumber = Cells(ZoneFromRow, 4)
Do
ZoneFromRow = ZoneFromRow + 1
If Cells(ZoneFromRow, 4) <> "Zone" & ZoneNumber(variable) Then
ZoneNumber = ZoneNumber + 1
"Zone"& ZoneNumber=Cells(ZoneFromRow, 4)
End If
Loop Until "Zone" & ZoneNumber = ""
------------------------------------------------------------------------------------------------

Apparently i am doing this wrong. If someone could please inform me on how
to properly dimension this (these) variable(s) I would be greatly apreciative.

Thanks

Ironhydroxide

 

[Joel]

Your problem is your loop may never end and it will loop forever. Zone and
Zone number only change when the IF condition is true. If you don't get into
the IF statement you end up in an endless loop. I can't tell wha you are
trying to do with this code so I can't recommend a solution.

 

[Bernie Deitrick]

Rusty,

Your code is such that I cannot tell what it is that you actually want to
do. Far better would be to describe (with words, and an example set of data)
what you want to do, and leave the coding to us.

HTH,
Bernie
MS Excel MVP

 

[ironhydroxide]

Not to be rude, but if i left the coding to you then i would never learn how
to code it correctly.

Anyways, i have a list in a Previously selected sheet (still selected when
the code encounters the Do...Loop statement) I want to select the first
cell, save that to a variable called Zone1 (or ZoneOne, Whichever) and then
incriment to get one row down and compare this information with the
information already saved in the Zone1 (ZoneOne) Variable, If the
information is different, then i want the loop to declare a new variable
called Zone2 (Or ZoneTwo) and set it to the information in the cell, If it
is the same i want to incriment and move on, Continueing untill there is no
more information in the Cell (The list is continuous and is sorted
Alphabetically according to the column with the pertinent information)

The Variables I would like to create are almost indefinate (sp) as in, the
amount of needed variables is different every time this instance is run, But
i will never need over 100 variables.

In reply to the First reply,

The Loop would only run until the List has run out of information (or hits a
blank cell) then it would assign "" to the variable and satisfy the Loop
Until Variable is ""


I hope that this Explaination Helps

Ironhydroxide

 

[Bernie Deitrick]

Rusty,

Yes, if you left _all_ the coding to us, then you would never learn - but
you tried, and I will code my response in the way that you were approaching
the problem to show how to do it in that style. Note that the code that I
am showing you is NOT the best way to perform what you want to do - using
advanced filters to show unique values only is the best way - but I hope you
learn something. I have commented the code to help you understand. Also,
this is not bullet-proof code....

HTH,
Bernie
MS Excel MVP

Sub FindUniqueValues()

Dim myR As Range
Dim myVal() As Variant
Dim i As Integer
Dim j As Integer
Dim Msg As String

'Start with the array set to the value of the activecell
Set myR = ActiveCell
ReDim myVal(1 To 1)
myVal(1) = ActiveCell.Value
i = 1
While myR.Value <> ""
'Check to see if the value has not been found (Match returns an
error)
'if it hasn't been found, expand the array and add the value
If IsError(Application.Match(myR.Value, myVal, False)) Then
'increment the counter
i = i + 1
'this expands the array without erasing the previous values
ReDim Preserve myVal(1 To i)
'add the value to the array
myVal(i) = myR.Value
End If
'go to the next cell down
Set myR = myR.Offset(1, 0)
Wend

'display the values that were found -
'this will show all the values in groups of up to 25,
'to show you how to access the values that were found

Msg = "I found " & UBound(myVal) & " unique values." & Chr(10) & _
"Here are 1 to " & _
Application.Min(UBound(myVal), 25) & ":" & Chr(10)
For i = LBound(myVal) To UBound(myVal)
Msg = Msg & Chr(10) & myVal(i)
If i Mod 25 = 0 Then
MsgBox Msg
Msg = "I found " & UBound(myVal) & _
" unique values." & Chr(10) & _
"Here are " & i + 1 & " to " & _
Application.Min(UBound(myVal), i + 25) & ":" & Chr(10)
End If
Next i
If UBound(myVal) Mod 25 <> 0 Then MsgBox Msg


End Sub

 

[ironhydroxide]

I have read up a bit, and apparently what i am looking for is to delcare a
variable that takes its name from a string. like "String1" then taking that
string and making a variable from it... String1=.....

I think this is called a Dynamic Variable

 

[ironhydroxide]

With this is there then a way to select the second, (for that matter any of
the) Cell in the List? I know i am way out of my ball game here, I only
understand about 1/2 of the code that you posted (although it works how you
say it should)

 

[Bernie Deitrick]

The second value is

myVal(2)

To find the cell with the first of those values, use

myR.EntireColumn.Find(myVal(2)).Select

HTH,
Bernie
MS Excel MVP

 

[ironhydroxide]

So the values are just a String in an array (the list?)? and not objects that
one can select to get the origional Cell From.? (If i understand correctly)
Thank You.
I had hoped that there was a way to do this without having to declare 100
variables and go through 100 If...Then Statements.
You really know your stuff. Thanks Again

Ironhydroxide

Btw. how did you know my first name?

 

[Bernie Deitrick]

You really know your stuff. Thanks Again

You're welcome, and thanks for letting me know that you were able to get a solution.

Ironhydroxide
Btw. how did you know my first name?

I used to be pretty good at chemistry, too...

Bernie
MS Excel MVP