Cell References and Looping

  • Thread starter Thread starter K.Paul
  • Start date Start date
K

K.Paul

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?
 
There are many ways to loop through ranges of cells. One way, show below, is
to use the row number in a For loop:

Dim RowNdx As Long
For RowNdx = 1 To 10
Cells(RowNdx, "A").Value = Cells(RowNdx, "B").Value
Next RowNdx

Another way, below, is to use a For Each to loop through rows in a
pre-defined range of cells. E.g.,

Dim Rng As Range
For Each Rng In Range("A1:A10")
Rng.Value = Rng(1, 2).Value
Next Rng


Perhaps if you provide more specific details about what you want to do,
someone can help you with the most appropriate mechanism for looping.
 
Well, here is a snippet of this very cumbersome code:
'First I set up a file:
Set fs = CreateObject("Scripting.FileSystemObject")
Set a = fs.CreateTextFile("C:\Guide.TXT", True)
'Then because this is all about parsing data into HTML, I
write the table header information
Range("B37").Select
Selection.Copy
a.write Range("B37")
' Now I start at row 62 and have several cells of data I
copy to a little table (replaced each time). There is a
cell of html tags before and after each data cell.
If Range("B62") = 0 Then GoTo LastLine
Range("B62").Select
Application.CutCopyMode = False
Selection.Copy
Range("C39").Select
ActiveSheet.Paste
Range("C62").Select
Application.CutCopyMode = False
Selection.Copy
Range("C42").Select
ActiveSheet.Paste
Range("C45").Select
ActiveSheet.Paste
Range("C48").Select
ActiveSheet.Paste
Range("C51").Select
ActiveSheet.Paste
Range("D62").Select
Application.CutCopyMode = False
Selection.Copy
Range("C54").Select
ActiveSheet.Paste
Range("E62").Select
Application.CutCopyMode = False
Selection.Copy
Range("C57").Select
ActiveSheet.Paste
Range("B60").Select
Selection.Copy
Range("B82").Select
Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _:=False,
Transpose:=False
' Then I write to the text file a line that is a
concatenation of each cell values from this table
a.write Range("B60")

rinse and repeat... problem is I have several hundred
lines in different ranges all over the spreadsheet to do
this with. I'd like a simple way to point the first row
of data, perform the copy/paste/write and then increment
to the next row. Then simply change the range parameters
and repeat in a different section of the spreadsheet.
Hope that makes sense...
 
Back
Top