syntax for relative cell change and looping

  • Thread starter Thread starter BwanaP
  • Start date Start date
B

BwanaP

I did some macros years ago mostly by recording, but I remember some
of the code looking different. I have a list of maybe 30 items in a
column, the next column is used to select those items. I want to check
if an item is selected and if so copy it to another part of the sheet
(which would be the defined print range). I know this should be very
easy, but can't remember how to loop (and how to end it) and increment
a relative cell change.

If there is somewhere I can find a listing of commands online that
would be helpful. I live overseas so can't just go out and buy a book
on this.
 
you can go into the VBE (alt+F11) and click on the help button in the menu.

looping is done with a For each construct

Sub Tester1()
dim rng as Range
dim cell as Range
for each cell in Range("A1:A100")
if cell.offset(0,1).Value = "x" then
if rng is nothing then
set rng = cell
else
set rng = union(rng,cell)
end if
Next cell
if not rng is nothing then
rng.copy Destination:=worksheets("sheet3").Range("A1")
End if
End Sub

or a Do ... Loop construct

There is also a While . . . Wend, but usually people use Do ... Loop as it
is more flexible.

If you don't have a collection like above, you can also use a for each with
a counter

for i = 1 to 100

Next i
 
Back
Top