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

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" wrote in message
...
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

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" wrote:

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" wrote in message
...
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

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" wrote in message
...
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" wrote:

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" wrote in
message
...
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

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" wrote:

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" wrote in message
...
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" wrote:

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" wrote in
message
...
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

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" wrote in message
...
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" wrote:

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" wrote in
message
...
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" wrote:

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" wrote in
message
...
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

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" wrote:

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" wrote:

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" wrote in message
...
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

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.
"ironhydroxide" wrote:

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