Hi,

i am trying to loop through a set of variables to find and empty one. if it
is empty, fill it with data from the current cell. if it has data, move on to
the next variable until an empty one is found.

i have my variables named

CL1
CL2
CL3

right through to 2000.

i have a procedure that will clear all of the variables

CL1 = Empty
CL2 = Empty
all the way to 2000

my current code is

If CL1 = Empty Then
CL1 = check
Exit Sub
End If
If CL1 = check Then
Exit Sub
End If

( check is a value that is collected from a procedure that runs down a
column and gets the cell data. the idea is, it runs down the column, gets the
data, checks if the values match. if they do, exit, if they dont, move to the
next available empty variable to store the value )

from here, the procedure finishes and then places the collected values onto
a spreadsheet. i suppose its a bit like filtering as i only need 1 instance
of the value shown whereas the list that is check may have several.

hope this is clear enough to understand.

i thought about

for i = 1 to 4000

etc but i cannot get anything to work!!



many thanks,

Nigel

i currently havent got one to loop these yet!

Hi.
Maybe, you could use an array variable :
Dim CL(1 to 4000)

and use :

For i= 1 to 4000
If CL(i)=...
Next i

Regards.
Daniel

Hi,

i am trying to loop through a set of variables to find and empty one. if it
is empty, fill it with data from the current cell. if it has data, move on to
the next variable until an empty one is found.

i have my variables named

CL1
CL2
CL3

right through to 2000.

i have a procedure that will clear all of the variables

CL1 = Empty
CL2 = Empty
all the way to 2000

my current code is

If CL1 = Empty Then
CL1 = check
Exit Sub
End If
If CL1 = check Then
Exit Sub
End If

( check is a value that is collected from a procedure that runs down a
column and gets the cell data. the idea is, it runs down the column, gets the
data, checks if the values match. if they do, exit, if they dont, move to the
next available empty variable to store the value )

from here, the procedure finishes and then places the collected values onto
a spreadsheet. i suppose its a bit like filtering as i only need 1 instance
of the value shown whereas the list that is check may have several.

hope this is clear enough to understand.

i thought about

for i = 1 to 4000

etc but i cannot get anything to work!!



many thanks,

Nigel

i currently havent got one to loop these yet!

Hi Daniel,

thanks for the reply. still not quite working so i thought id add a bit more
code-

this procedure below collects the value from every cell in the range and
checks it against the variable value stored to prevent duplication:

Sub Start()
Dim CL(1 To 20)
Application.ScreenUpdating = False
For i = 1 To 20
Range("A" & i).Select
check = ActiveCell.Value
RunCheck
Next i
FilCliChart
Application.ScreenUpdating = True
End Sub

The next code "RunCheck" is what checks the variable list. this is a list
that i have declared at the top of the module and made public

Sub RunCheck()
Dim CL(1 To 20)
For i = 1 To 20
If CL(i) = Empty Then
CL(i) = check
Exit Sub
End If
If CL(i) = check Then
Exit Sub
End If
Next i

End Sub

and finally, the code the places the data onto the worksheet:

Sub FilCliChart()

Range("I5").Select
ActiveCell.Value = CL2

Range("I6").Select
ActiveCell.Value = CL3

Range("I7").Select
ActiveCell.Value = CL4

Range("I8").Select
ActiveCell.Value = CL5

Range("I9").Select
ActiveCell.Value = CL6

Range("I10").Select
ActiveCell.Value = CL7

Range("I11").Select
ActiveCell.Value = CL8

Range("I12").Select
ActiveCell.Value = CL9

Range("I13").Select
ActiveCell.Value = CL10

Range("I14").Select
ActiveCell.Value = CL11

Range("I15").Select
ActiveCell.Value = CL12

Range("I16").Select
ActiveCell.Value = CL13

Range("I17").Select
ActiveCell.Value = CL14

Range("I18").Select
ActiveCell.Value = CL15

Range("I19").Select
ActiveCell.Value = CL16

Range("I20").Select
ActiveCell.Value = CL17

Range("I21").Select
ActiveCell.Value = CL18

Range("I22").Select
ActiveCell.Value = CL19

Range("I23").Select
ActiveCell.Value = CL20

Range("I24").Select
ActiveCell.Value = CL21

Range("I25").Select
ActiveCell.Value = CL22

Range("I26").Select
ActiveCell.Value = CL23

Range("I27").Select
ActiveCell.Value = CL24

Range("I28").Select
ActiveCell.Value = CL25

Range("I29").Select
ActiveCell.Value = CL26

Range("I30").Select
ActiveCell.Value = CL27

Range("I31").Select
ActiveCell.Value = CL28

Range("I32").Select
ActiveCell.Value = CL29

Range("I33").Select
ActiveCell.Value = CL30
End Sub

i was hoping to reduce this too but one ste pat a time :)

many thanks,

Nigel

"Daniel.C" wrote:

Hi.
Maybe, you could use an array variable :
Dim CL(1 to 4000)

and use :

For i= 1 to 4000
If CL(i)=...
Next i

Regards.
Daniel

Hi,

i am trying to loop through a set of variables to find and empty one. if it
is empty, fill it with data from the current cell. if it has data, move on to
the next variable until an empty one is found.

i have my variables named

CL1
CL2
CL3

right through to 2000.

i have a procedure that will clear all of the variables

CL1 = Empty
CL2 = Empty
all the way to 2000

my current code is

If CL1 = Empty Then
CL1 = check
Exit Sub
End If
If CL1 = check Then
Exit Sub
End If

( check is a value that is collected from a procedure that runs down a
column and gets the cell data. the idea is, it runs down the column, gets the
data, checks if the values match. if they do, exit, if they dont, move to the
next available empty variable to store the value )

from here, the procedure finishes and then places the collected values onto
a spreadsheet. i suppose its a bit like filtering as i only need 1 instance
of the value shown whereas the list that is check may have several.

hope this is clear enough to understand.

i thought about

for i = 1 to 4000

etc but i cannot get anything to work!!



many thanks,

Nigel

i currently havent got one to loop these yet!

You may use :

Public CL(1 To 20)
Sub Start()
Dim check
Application.ScreenUpdating = False
For i = 1 To 20
Range("A" & i).Select
check = ActiveCell.Value
RunCheck check
Next i
'FilCliChart
Application.ScreenUpdating = True
Var = CL
End Sub
Sub RunCheck(check)
For i = 1 To 20
If CL(i) = "" Then
CL(i) = check
Exit Sub
End If
If CL(i) = check Then
Exit Sub
End If
Next i

End Sub

"CL" is public variable and must be pasted on top of a module. I am
assuming that you use "empty" instead of "" to check if the cell is
empty.

Another way to elminate duplicates is to use a collection :

Sub test()
Dim CL(1 To 20)
Dim c As Range, Coll As New Collection
On Error Resume Next
For Each c In [A1:A20]
Coll.Add c.Value, c.Value
'if dealing with numbers, replace with :
'Coll.Add cstr(c.Value), cstr(c.Value)
Next
On Error GoTo 0
For Each Item In Coll
i = i + 1
CL(i) = Item
'or, with numbers
'CL(i) = CDbl(Coll(i))
Next Item
End Sub

Daniel

Hi Daniel,

thanks for the reply. still not quite working so i thought id add a bit more
code-

this procedure below collects the value from every cell in the range and
checks it against the variable value stored to prevent duplication:

Sub Start()
Dim CL(1 To 20)
Application.ScreenUpdating = False
For i = 1 To 20
Range("A" & i).Select
check = ActiveCell.Value
RunCheck
Next i
FilCliChart
Application.ScreenUpdating = True
End Sub

The next code "RunCheck" is what checks the variable list. this is a list
that i have declared at the top of the module and made public

Sub RunCheck()
Dim CL(1 To 20)
For i = 1 To 20
If CL(i) = Empty Then
CL(i) = check
Exit Sub
End If
If CL(i) = check Then
Exit Sub
End If
Next i

End Sub

and finally, the code the places the data onto the worksheet:

Sub FilCliChart()

Range("I5").Select
ActiveCell.Value = CL2

Range("I6").Select
ActiveCell.Value = CL3

Range("I7").Select
ActiveCell.Value = CL4

Range("I8").Select
ActiveCell.Value = CL5

Range("I9").Select
ActiveCell.Value = CL6

Range("I10").Select
ActiveCell.Value = CL7

Range("I11").Select
ActiveCell.Value = CL8

Range("I12").Select
ActiveCell.Value = CL9

Range("I13").Select
ActiveCell.Value = CL10

Range("I14").Select
ActiveCell.Value = CL11

Range("I15").Select
ActiveCell.Value = CL12

Range("I16").Select
ActiveCell.Value = CL13

Range("I17").Select
ActiveCell.Value = CL14

Range("I18").Select
ActiveCell.Value = CL15

Range("I19").Select
ActiveCell.Value = CL16

Range("I20").Select
ActiveCell.Value = CL17

Range("I21").Select
ActiveCell.Value = CL18

Range("I22").Select
ActiveCell.Value = CL19

Range("I23").Select
ActiveCell.Value = CL20

Range("I24").Select
ActiveCell.Value = CL21

Range("I25").Select
ActiveCell.Value = CL22

Range("I26").Select
ActiveCell.Value = CL23

Range("I27").Select
ActiveCell.Value = CL24

Range("I28").Select
ActiveCell.Value = CL25

Range("I29").Select
ActiveCell.Value = CL26

Range("I30").Select
ActiveCell.Value = CL27

Range("I31").Select
ActiveCell.Value = CL28

Range("I32").Select
ActiveCell.Value = CL29

Range("I33").Select
ActiveCell.Value = CL30
End Sub

i was hoping to reduce this too but one ste pat a time :)

many thanks,

Nigel

"Daniel.C" wrote:

Hi.
Maybe, you could use an array variable :
Dim CL(1 to 4000)

and use :

For i= 1 to 4000
If CL(i)=...
Next i

Regards.
Daniel

Hi,

i am trying to loop through a set of variables to find and empty one. if it
is empty, fill it with data from the current cell. if it has data, move on
to the next variable until an empty one is found.

i have my variables named

CL1
CL2
CL3

right through to 2000.

i have a procedure that will clear all of the variables

CL1 = Empty
CL2 = Empty
all the way to 2000

my current code is

If CL1 = Empty Then
CL1 = check
Exit Sub
End If
If CL1 = check Then
Exit Sub
End If

( check is a value that is collected from a procedure that runs down a
column and gets the cell data. the idea is, it runs down the column, gets
the data, checks if the values match. if they do, exit, if they dont, move
to the next available empty variable to store the value )

from here, the procedure finishes and then places the collected values onto
a spreadsheet. i suppose its a bit like filtering as i only need 1 instance
of the value shown whereas the list that is check may have several.

hope this is clear enough to understand.

i thought about

for i = 1 to 4000

etc but i cannot get anything to work!!



many thanks,

Nigel

i currently havent got one to loop these yet!

Nigel, I'm somewhat confused by the information you posted.

What is "Empty". Is that another constant or variable you have declared?

Typically, working with a range of cells on a worksheet, these snippets are
effective ways of doing it:

Dim myListRange As Range
Dim anyListEntry As Range

Set myListRange = ThisWorkbook.Worksheets("sheet name"). _
Range("CL1:CL2000")
'clear them all in one instruction
myListRange.ClearContents ' clear contents but not formatting
or
myListRange.ClearAll ' wipes out contents, formulas, formatting, etc.

It leaves myListRange empty, and available to be referenced in the same code
segment later on if necessary.

I'm confused about what value is in "check" or if you mean to pick it up
from a value in a cell on the sheet somewhere.

In your first routine, RunCheck(), it is going to probably jump out of the
sub before going all the way from 1 to 20: Your first test seems to set that
up. By using
Dim CL(1 to 20)
you have declared an array with 20 empty 'cells' or elements.
So, the first time thru the loop:
If CL(i) = Empty Then ' what is "Empty" but it is probably going to be
empty!
CL(i) = check ' fine, first time thru CL(1) gets set to 'check'
Exit Sub ' and you jump out of the loop and even out of the sub
End If
Each time you re-enter the routine, CL(1 to 20) are cleared of their
previous contents, so you never get beyond element 1!


If I were working in code and had one column with many entries and wanted to
just extract individual entries from it, one way to do that would be with
code like this. This assumes that the source list is in column A, and we
want to build the list of individual entries in column I. This code contains
some stuff your particular situation may not actually need, but I've included
it to kind of handle all situations, including putting the list of unique
entries on a separate worksheet, and with Const declarations that you can
change to adapt it to your situation.

Sub BuildUniqueEntryList()
'this routine will work through
'a list to identify unique entries
'in it and place those unique entries
'into another column
'The unique list may be on the same
'sheet or on another, depending on
'the definitions of the sheet names
'
Const sourceSheetName = "Sheet1"
Const sourceColumnID = "A" ' change as required
Const firstSourceRowUsed = 1 ' change as required
'next may be same name as sourceSheetName
'or different if the unique list is to
'appear on a different sheet
Const destSheetName = "Sheet2"
Const destColumnID = "I" ' change as required
Const destFirstRowToUse = 1 ' change as required

Dim sourceWS As Worksheet
Dim sourceListRange As Range
Dim anySourceEntry As Range
Dim destWS As Worksheet
Dim destList As Range
Dim anyDestEntry As Range
Dim anyRowPointer As Long
Dim matchedFlag As Boolean

Set sourceWS = Worksheets(sourceSheetName)
'do we have any work to do at all?
If sourceWS.Range(sourceColumnID & Rows.Count). _
End(xlUp).Row < firstSourceRowUsed Then
'nothing to do, just quit
Set sourceWS = Nothing
Exit Sub
End If
Set sourceListRange = sourceWS.Range(sourceColumnID & _
firstSourceRowUsed & ":" & _
sourceWS.Range(sourceColumnID & Rows.Count).End(xlUp).Address)
Set destWS = Worksheets(destSheetName)
'clear out any previous entries
'in the unique list area
anyRowPointer = destWS.Range(destColumnID & Rows.Count).End(xlUp).Row
If anyRowPointer < destFirstRowToUse Then
anyRowPointer = destFirstRowToUse
End If
destWS.Range(destColumnID & destFirstRowToUse & ":" _
& destColumnID & anyRowPointer).Clear
'initialize destList
Set destList = destWS.Range(destColumnID & destFirstRowToUse _
& ":" & destColumnID & anyRowPointer)
'examine each entry in the source list and compare
'it to entries in the unique list and if it is not
'found in the unique list, add it to the unique list
For Each anySourceEntry In sourceListRange
If Not IsEmpty(anySourceEntry) Then
matchedFlag = False ' initialize/reset
For Each anyDestEntry In destList
If anySourceEntry = anyDestEntry Then
matchedFlag = True
Exit For
End If
Next ' end of anyDestEntry loop
If Not matchedFlag Then
'this is a new unique entry
'place it at the end of the
'destList and redefine destList range
destWS.Range(destColumnID & Rows.Count).End(xlUp). _
Offset(1, 0) = anySourceEntry
'redefine destList to include the new entry
Set destList = destWS.Range(destColumnID & _
destFirstRowToUse & ":" & destWS.Range(destColumnID & _
Rows.Count).End(xlUp).Address)
End If
End If ' end of test for empty cell
Next ' end of anySourceEntry loop
'all finished, do cleanup
Set sourceListRange = Nothing
Set sourceWS = Nothing
Set destList = Nothing
Set destWS = Nothing
End Sub


For the code you posted, first declare the array CL(1 to 20) up in the same
area where you declared 'check'. It needs to be visible to all Subs in the
module also. Then remove the Dim CL(1 To 20) in Start() and in RunCheck().
As for the FilCliChart() you might try this (with the change to use CL(1 to
20) having been made:
Sub FilClIChart()
Dim LoopPointer As Integer
'in this case LBound(CL) = 1 and UBound(CL)=20
'automatically adapts if you change size of the array
Range("I5").Select
Application.ScreenUpdating=False
For LoopPointer = LBound(CL) to UBound(CL)
'activecell is I5
ActiveCell.Offset(LoopPointer-1,0) = CL(LoopPointer)
Next ' end of LoopPointer
End Sub

"NigelShaw" wrote:

Hi,

i am trying to loop through a set of variables to find and empty one. if it
is empty, fill it with data from the current cell. if it has data, move on to
the next variable until an empty one is found.

i have my variables named

CL1
CL2
CL3

right through to 2000.

i have a procedure that will clear all of the variables

CL1 = Empty
CL2 = Empty
all the way to 2000

my current code is

If CL1 = Empty Then
CL1 = check
Exit Sub
End If
If CL1 = check Then
Exit Sub
End If

( check is a value that is collected from a procedure that runs down a
column and gets the cell data. the idea is, it runs down the column, gets the
data, checks if the values match. if they do, exit, if they dont, move to the
next available empty variable to store the value )

from here, the procedure finishes and then places the collected values onto
a spreadsheet. i suppose its a bit like filtering as i only need 1 instance
of the value shown whereas the list that is check may have several.

hope this is clear enough to understand.

i thought about

for i = 1 to 4000

etc but i cannot get anything to work!!



many thanks,

Nigel

i currently havent got one to loop these yet!

Hi JLatham,

to ease the confusion somewhat, i have always used Empty. its nothing i
declare though. it seems that when i type empty ( even in lower case ), it
turns to Blue and changes to Proper case as any other object does?

ive never really questioned it before but as i just assumed it was the same
as Null & "". Alternatively, as the code sets the value as Empty and then
checks to see if the value is Empty, maybe its checking the string values and
matching them. if Empty = Empty then do something.

i will try both code examples and post back.

many thanks,

Nigel

In reference to clearing values in one instruction, its not the cell values
i want to clear. it is the VB values i have set. i need to keep the cell
values and check them against the CL values. i clear the values to give
myself a clean virtual list itemised from 1 to 2000. when my code gets a cell
value for example 002654554, it sets the variable 'Check to 002654554. then
the code goes to RunCheck. this should look through all of the variables CL1
to CL2000. if any variable contains 002654554, it exits and moves to the next
cell otherwise it finds the next Empty CL no and places the data into it.

"JLatham" wrote:

Nigel, I'm somewhat confused by the information you posted.

What is "Empty". Is that another constant or variable you have declared?

Typically, working with a range of cells on a worksheet, these snippets are
effective ways of doing it:

Dim myListRange As Range
Dim anyListEntry As Range

Set myListRange = ThisWorkbook.Worksheets("sheet name"). _
Range("CL1:CL2000")
'clear them all in one instruction
myListRange.ClearContents ' clear contents but not formatting
or
myListRange.ClearAll ' wipes out contents, formulas, formatting, etc.

It leaves myListRange empty, and available to be referenced in the same code
segment later on if necessary.

I'm confused about what value is in "check" or if you mean to pick it up
from a value in a cell on the sheet somewhere.

In your first routine, RunCheck(), it is going to probably jump out of the
sub before going all the way from 1 to 20: Your first test seems to set that
up. By using
Dim CL(1 to 20)
you have declared an array with 20 empty 'cells' or elements.
So, the first time thru the loop:
If CL(i) = Empty Then ' what is "Empty" but it is probably going to be
empty!
CL(i) = check ' fine, first time thru CL(1) gets set to 'check'
Exit Sub ' and you jump out of the loop and even out of the sub
End If
Each time you re-enter the routine, CL(1 to 20) are cleared of their
previous contents, so you never get beyond element 1!


If I were working in code and had one column with many entries and wanted to
just extract individual entries from it, one way to do that would be with
code like this. This assumes that the source list is in column A, and we
want to build the list of individual entries in column I. This code contains
some stuff your particular situation may not actually need, but I've included
it to kind of handle all situations, including putting the list of unique
entries on a separate worksheet, and with Const declarations that you can
change to adapt it to your situation.

Sub BuildUniqueEntryList()
'this routine will work through
'a list to identify unique entries
'in it and place those unique entries
'into another column
'The unique list may be on the same
'sheet or on another, depending on
'the definitions of the sheet names
'
Const sourceSheetName = "Sheet1"
Const sourceColumnID = "A" ' change as required
Const firstSourceRowUsed = 1 ' change as required
'next may be same name as sourceSheetName
'or different if the unique list is to
'appear on a different sheet
Const destSheetName = "Sheet2"
Const destColumnID = "I" ' change as required
Const destFirstRowToUse = 1 ' change as required

Dim sourceWS As Worksheet
Dim sourceListRange As Range
Dim anySourceEntry As Range
Dim destWS As Worksheet
Dim destList As Range
Dim anyDestEntry As Range
Dim anyRowPointer As Long
Dim matchedFlag As Boolean

Set sourceWS = Worksheets(sourceSheetName)
'do we have any work to do at all?
If sourceWS.Range(sourceColumnID & Rows.Count). _
End(xlUp).Row < firstSourceRowUsed Then
'nothing to do, just quit
Set sourceWS = Nothing
Exit Sub
End If
Set sourceListRange = sourceWS.Range(sourceColumnID & _
firstSourceRowUsed & ":" & _
sourceWS.Range(sourceColumnID & Rows.Count).End(xlUp).Address)
Set destWS = Worksheets(destSheetName)
'clear out any previous entries
'in the unique list area
anyRowPointer = destWS.Range(destColumnID & Rows.Count).End(xlUp).Row
If anyRowPointer < destFirstRowToUse Then
anyRowPointer = destFirstRowToUse
End If
destWS.Range(destColumnID & destFirstRowToUse & ":" _
& destColumnID & anyRowPointer).Clear
'initialize destList
Set destList = destWS.Range(destColumnID & destFirstRowToUse _
& ":" & destColumnID & anyRowPointer)
'examine each entry in the source list and compare
'it to entries in the unique list and if it is not
'found in the unique list, add it to the unique list
For Each anySourceEntry In sourceListRange
If Not IsEmpty(anySourceEntry) Then
matchedFlag = False ' initialize/reset
For Each anyDestEntry In destList
If anySourceEntry = anyDestEntry Then
matchedFlag = True
Exit For
End If
Next ' end of anyDestEntry loop
If Not matchedFlag Then
'this is a new unique entry
'place it at the end of the
'destList and redefine destList range
destWS.Range(destColumnID & Rows.Count).End(xlUp). _
Offset(1, 0) = anySourceEntry
'redefine destList to include the new entry
Set destList = destWS.Range(destColumnID & _
destFirstRowToUse & ":" & destWS.Range(destColumnID & _
Rows.Count).End(xlUp).Address)
End If
End If ' end of test for empty cell
Next ' end of anySourceEntry loop
'all finished, do cleanup
Set sourceListRange = Nothing
Set sourceWS = Nothing
Set destList = Nothing
Set destWS = Nothing
End Sub


For the code you posted, first declare the array CL(1 to 20) up in the same
area where you declared 'check'. It needs to be visible to all Subs in the
module also. Then remove the Dim CL(1 To 20) in Start() and in RunCheck().
As for the FilCliChart() you might try this (with the change to use CL(1 to
20) having been made:
Sub FilClIChart()
Dim LoopPointer As Integer
'in this case LBound(CL) = 1 and UBound(CL)=20
'automatically adapts if you change size of the array
Range("I5").Select
Application.ScreenUpdating=False
For LoopPointer = LBound(CL) to UBound(CL)
'activecell is I5
ActiveCell.Offset(LoopPointer-1,0) = CL(LoopPointer)
Next ' end of LoopPointer
End Sub

"NigelShaw" wrote:

Hi,

i am trying to loop through a set of variables to find and empty one. if it
is empty, fill it with data from the current cell. if it has data, move on to
the next variable until an empty one is found.

i have my variables named

CL1
CL2
CL3

right through to 2000.

i have a procedure that will clear all of the variables

CL1 = Empty
CL2 = Empty
all the way to 2000

my current code is

If CL1 = Empty Then
CL1 = check
Exit Sub
End If
If CL1 = check Then
Exit Sub
End If

( check is a value that is collected from a procedure that runs down a
column and gets the cell data. the idea is, it runs down the column, gets the
data, checks if the values match. if they do, exit, if they dont, move to the
next available empty variable to store the value )

from here, the procedure finishes and then places the collected values onto
a spreadsheet. i suppose its a bit like filtering as i only need 1 instance
of the value shown whereas the list that is check may have several.

hope this is clear enough to understand.

i thought about

for i = 1 to 4000

etc but i cannot get anything to work!!



many thanks,

Nigel

i currently havent got one to loop these yet!

That works for me! Probably just one of those VB pre-defined constants I've
either forgotten about or just hadn't ever realized was there for use.
There's a lot of closet doors I have yet to open, even after living in the
Excel house for many, many years.

"NigelShaw" wrote:

Hi JLatham,

to ease the confusion somewhat, i have always used Empty. its nothing i
declare though. it seems that when i type empty ( even in lower case ), it
turns to Blue and changes to Proper case as any other object does?

ive never really questioned it before but as i just assumed it was the same
as Null & "". Alternatively, as the code sets the value as Empty and then
checks to see if the value is Empty, maybe its checking the string values and
matching them. if Empty = Empty then do something.

i will try both code examples and post back.

many thanks,

Nigel

In reference to clearing values in one instruction, its not the cell values
i want to clear. it is the VB values i have set. i need to keep the cell
values and check them against the CL values. i clear the values to give
myself a clean virtual list itemised from 1 to 2000. when my code gets a cell
value for example 002654554, it sets the variable 'Check to 002654554. then
the code goes to RunCheck. this should look through all of the variables CL1
to CL2000. if any variable contains 002654554, it exits and moves to the next
cell otherwise it finds the next Empty CL no and places the data into it.

"JLatham" wrote:

Nigel, I'm somewhat confused by the information you posted.

What is "Empty". Is that another constant or variable you have declared?

Typically, working with a range of cells on a worksheet, these snippets are
effective ways of doing it:

Dim myListRange As Range
Dim anyListEntry As Range

Set myListRange = ThisWorkbook.Worksheets("sheet name"). _
Range("CL1:CL2000")
'clear them all in one instruction
myListRange.ClearContents ' clear contents but not formatting
or
myListRange.ClearAll ' wipes out contents, formulas, formatting, etc.

It leaves myListRange empty, and available to be referenced in the same code
segment later on if necessary.

I'm confused about what value is in "check" or if you mean to pick it up
from a value in a cell on the sheet somewhere.

In your first routine, RunCheck(), it is going to probably jump out of the
sub before going all the way from 1 to 20: Your first test seems to set that
up. By using
Dim CL(1 to 20)
you have declared an array with 20 empty 'cells' or elements.
So, the first time thru the loop:
If CL(i) = Empty Then ' what is "Empty" but it is probably going to be
empty!
CL(i) = check ' fine, first time thru CL(1) gets set to 'check'
Exit Sub ' and you jump out of the loop and even out of the sub
End If
Each time you re-enter the routine, CL(1 to 20) are cleared of their
previous contents, so you never get beyond element 1!


If I were working in code and had one column with many entries and wanted to
just extract individual entries from it, one way to do that would be with
code like this. This assumes that the source list is in column A, and we
want to build the list of individual entries in column I. This code contains
some stuff your particular situation may not actually need, but I've included
it to kind of handle all situations, including putting the list of unique
entries on a separate worksheet, and with Const declarations that you can
change to adapt it to your situation.

Sub BuildUniqueEntryList()
'this routine will work through
'a list to identify unique entries
'in it and place those unique entries
'into another column
'The unique list may be on the same
'sheet or on another, depending on
'the definitions of the sheet names
'
Const sourceSheetName = "Sheet1"
Const sourceColumnID = "A" ' change as required
Const firstSourceRowUsed = 1 ' change as required
'next may be same name as sourceSheetName
'or different if the unique list is to
'appear on a different sheet
Const destSheetName = "Sheet2"
Const destColumnID = "I" ' change as required
Const destFirstRowToUse = 1 ' change as required

Dim sourceWS As Worksheet
Dim sourceListRange As Range
Dim anySourceEntry As Range
Dim destWS As Worksheet
Dim destList As Range
Dim anyDestEntry As Range
Dim anyRowPointer As Long
Dim matchedFlag As Boolean

Set sourceWS = Worksheets(sourceSheetName)
'do we have any work to do at all?
If sourceWS.Range(sourceColumnID & Rows.Count). _
End(xlUp).Row < firstSourceRowUsed Then
'nothing to do, just quit
Set sourceWS = Nothing
Exit Sub
End If
Set sourceListRange = sourceWS.Range(sourceColumnID & _
firstSourceRowUsed & ":" & _
sourceWS.Range(sourceColumnID & Rows.Count).End(xlUp).Address)
Set destWS = Worksheets(destSheetName)
'clear out any previous entries
'in the unique list area
anyRowPointer = destWS.Range(destColumnID & Rows.Count).End(xlUp).Row
If anyRowPointer < destFirstRowToUse Then
anyRowPointer = destFirstRowToUse
End If
destWS.Range(destColumnID & destFirstRowToUse & ":" _
& destColumnID & anyRowPointer).Clear
'initialize destList
Set destList = destWS.Range(destColumnID & destFirstRowToUse _
& ":" & destColumnID & anyRowPointer)
'examine each entry in the source list and compare
'it to entries in the unique list and if it is not
'found in the unique list, add it to the unique list
For Each anySourceEntry In sourceListRange
If Not IsEmpty(anySourceEntry) Then
matchedFlag = False ' initialize/reset
For Each anyDestEntry In destList
If anySourceEntry = anyDestEntry Then
matchedFlag = True
Exit For
End If
Next ' end of anyDestEntry loop
If Not matchedFlag Then
'this is a new unique entry
'place it at the end of the
'destList and redefine destList range
destWS.Range(destColumnID & Rows.Count).End(xlUp). _
Offset(1, 0) = anySourceEntry
'redefine destList to include the new entry
Set destList = destWS.Range(destColumnID & _
destFirstRowToUse & ":" & destWS.Range(destColumnID & _
Rows.Count).End(xlUp).Address)
End If
End If ' end of test for empty cell
Next ' end of anySourceEntry loop
'all finished, do cleanup
Set sourceListRange = Nothing
Set sourceWS = Nothing
Set destList = Nothing
Set destWS = Nothing
End Sub


For the code you posted, first declare the array CL(1 to 20) up in the same
area where you declared 'check'. It needs to be visible to all Subs in the
module also. Then remove the Dim CL(1 To 20) in Start() and in RunCheck().
As for the FilCliChart() you might try this (with the change to use CL(1 to
20) having been made:
Sub FilClIChart()
Dim LoopPointer As Integer
'in this case LBound(CL) = 1 and UBound(CL)=20
'automatically adapts if you change size of the array
Range("I5").Select
Application.ScreenUpdating=False
For LoopPointer = LBound(CL) to UBound(CL)
'activecell is I5
ActiveCell.Offset(LoopPointer-1,0) = CL(LoopPointer)
Next ' end of LoopPointer
End Sub

"NigelShaw" wrote:

Hi,

i am trying to loop through a set of variables to find and empty one. if it
is empty, fill it with data from the current cell. if it has data, move on to
the next variable until an empty one is found.

i have my variables named

CL1
CL2
CL3

right through to 2000.

i have a procedure that will clear all of the variables

CL1 = Empty
CL2 = Empty
all the way to 2000

my current code is

If CL1 = Empty Then
CL1 = check
Exit Sub
End If
If CL1 = check Then
Exit Sub
End If

( check is a value that is collected from a procedure that runs down a
column and gets the cell data. the idea is, it runs down the column, gets the
data, checks if the values match. if they do, exit, if they dont, move to the
next available empty variable to store the value )

from here, the procedure finishes and then places the collected values onto
a spreadsheet. i suppose its a bit like filtering as i only need 1 instance
of the value shown whereas the list that is check may have several.

hope this is clear enough to understand.

i thought about

for i = 1 to 4000

etc but i cannot get anything to work!!



many thanks,

Nigel

i currently havent got one to loop these yet!