Cell References and Looping

  • Thread starter Thread starter KPaul
  • Start date Start date
K

KPaul

I have a Looping question.

I have several rows of data to manipulate.. and want an
easy way to increment a cell range. Can I use a variable
in the Cell range name itself..? For instance, on each
pass I copy the values in A1, B1, C1, etc.. someplace else
and do something then increment to A2, B2, C2 etc...

Can I replace the (row component of the cell reference)
e.g. 1, 2, 3 with an integer variable so that I can write
a simple do while or for next loop?
 
One way, among many:

Dim i As Long
Dim destRange As Range

Set destRange = Sheets("Sheet2").Range("A1")
For i = 1 To 20
Cells(i, 1).Resize(1, 3).Copy _
Destination:=destRange(i, 1)
Next i

(You can substitute Range("A" & i) for Cells(i, 1) if you want.)

another:

Dim cell As Range
Dim destRange As Range
Set destRange = Sheets("Sheet2").Range("A1")
For Each cell In Range("A1:A20")
cell.Resize(1, 3).Copy destRange
Set destRange = destRange.Offset(1, 0)
Next cell
 
I think somehting like this is what you are looking for:

rownum = 1
do until rownum = 10
Range("A" & rownum) = somevalue
rownum = rownum + 1
loop
 
Back
Top