"Do...Loop" and data type adding help

  • Thread starter Thread starter lykwid
  • Start date Start date
L

lykwid

private sub absent_click()

dim absentcell as range
dim row as integer
row = 61

activeworkbook.sheets(\"aerobics\").activate

range(\"g61\").select

do

do

if activecell.value = a then

activecell.offset(1, 0).select

row = row + 1

end if

loop until activecell.value <> a

set absentcell = activecell

activecell.offset(0, -2).select

selection.copy

range(\"b98\").select

do

if activecell.value <> \"\" then

activecell.offset(1, 0).select

end if

loop until activecell.value = \"\"

selection.pastespecial paste:=xlpastevalues, operation:=xlnone
skipblanks _
:=false, transpose:=false
application.cutcopymode = false

absentcell.select

activecell.value = \"absent\"

loop until row > 90

end sub

____________________________________________________________________________________________________________________________________________________________

The function should do as follows:

Go to top of table (cell G61).

If the cell has "a" in, put the cell 2 spaces to the left into anothe
table (B98). Otherwise, move down a cell.
Repeat until you move further than row 90.

The function works perfectly, except it doesn't stop searching when i
reaches row 90, which is what I would expect since I + 1 to the ro
integer each time the offset moves down a row.

Any help would be very helpful
:rolleyes: :rolleyes: ;)

Thanks :confused
 
Yay! I have it working. Largely with thanks to Tom Ogilvy.

private sub absent_click()

dim absentcell as range
dim row as integer
row = 61

activeworkbook.sheets(\"aerobics\").activate

range(\"g61\").select

a = \"a\"

do

do

if activecell.value <> a then

activecell.offset(1, 0).select

row = row + 1

if row > 90 then exit sub

end if

loop until activecell.value = a

set absentcell = activecell

activecell.offset(0, -2).select

selection.copy

range(\"b98\").select

do

if activecell.value <> \"\" then

activecell.offset(1, 0).select

end if

loop until activecell.value = \"\"

selection.pastespecial paste:=xlpastevalues, operation:=xlnone
skipblanks _
:=false, transpose:=false
application.cutcopymode = false

absentcell.select

activecell.value = \"absent\"

activecell.offset(1, 0).select

row = row + 1

if row > 90 then

exit sub

end if

loop until row > 90

end su
 
Back
Top