Re: Looping Through Variables

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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!





.

Relevant Pages