Loop a

  • Thread starter Thread starter Ben
  • Start date Start date
B

Ben

Guys
I am programming up a macro in Excel, I was just wondering how to loop
the range function.

Whereby I would like to somehow put a variable in the function Range,
for a set column, eg.

dim i as integer
dim c(0) as string
i=0


for i =117 to 200
c(0) = Range("C" & i) ' This is the line that doesn't work
msgbox c(0) 'for simplicity

next i

Thanks
 
No need for a loop

Dim c

c = Application.Transpose(Range("C117:C200"))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Instead of the range function you could use the cells function.

Example:

for i =117 to 200
c(0) = Cells(i, 3) ' This means cell with row i and column 3: when
i=117 it will be C117, C118... to C200
msgbox c(0)

Good Luck!
 
Guys
I am programming up a macro in Excel, I was just wondering how to loop
the range function.

Whereby I would like to somehow put a variable in the function Range,
for a set column, eg.

dim i as integer
dim c(0) as string
i=0

for i =117 to 200
c(0) = Range("C" & i) ' This is the line that doesn't work
msgbox c(0) 'for simplicity

next i

Thanks
Click to expand...

Hi Ben,

What do you mean when you say "doesn't work"?

Your code worked for me.

Ken Johnson
 
Dim i as long
dim c() as variant 'could be numbers or strings???
dim FirstNum as Long
dim LastNum as Long
firstnum = 117
lastnum = 200

redim c(firstnum to lastnum)
for i = lbound(c) to ubound(c)
c(i) = activesheet.range("C" & i).value
'or c(i) = activesheet.cells(i,"C").value
next i

======
This will create an array with lower bound 117 and upper bound 200:
dim C(117 to 200) as variant

If you really wanted to start at 0:
Dim i as long
dim c() as variant 'could be numbers or strings???
dim FirstNum as Long
dim LastNum as Long
firstnum = 117
lastnum = 200

redim c(0 to lastnum-firstnum)
for i = lbound(c) to ubound(c)
c(i) = activesheet.range("C" & i+firstnum).value
'or c(i) = activesheet.cells(i+firstnum,"C").value
next i

==========
Alternatively, you could pick up the values in a 2 dimensional array in one fell
swoop:

Dim c as variant
dim i as long
c = activesheet.range("C117:C200")
for i = lbound(c,1) to ubound(c,1)
msgbox c(i,1)
next i

C is a 2 dimensional range--84 rows by 1 column
kind of like:
dim c(1 to 84, 1 to 1) as variant
 
Back
Top