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
;)

Thanks :confused

--
Message posted from http://www.ExcelForum.com

I'm not the greatest expert on this stuff, but I've made a lot of mistakes
so far. 8) The first thing I see is that you increment your row
variable only if activecell.value = a, not at every row checked. If you
want to catch every row checked, you might be better off doing end if and
*then* row = row+1.

Actually, that might not do it either. If you don't check your row inside
your Do and you don't lose your a value for a while, you could see your last
cell at, say, row 84, then loop until you hit row 2000, and never drop out
of the Do loop to run the rest of the code.

So maybe:

End If
row = row + 1
If row90 Then
GoTo EndThis
End If

Loop Until ActiveCell.Value < a

' rest of code

EndThis:

End Sub

HTH
Ed

"lykwid " wrote in message
...
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 another
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 it
reaches row 90, which is what I would expect since I + 1 to the row
integer each time the offset moves down a row.

Any help would be very helpful
;)

Thanks


---
Message posted from http://www.ExcelForum.com/

**Changed name of post but it didn't update on forums. Hopefully thi
post will update it.*

--
Message posted from http://www.ExcelForum.com

Huge thanks for your suggestion, but I don't understand what you mean :(


---
Message posted from http://www.ExcelForum.com/

If everything works as designed *EXCEPT* the row count, then your problem
must be with how you are telling your code that you have reached the last
row. Your code:

if activecell.value = a then
activecell.offset(1, 0).select
row = row + 1
end if

will increment the variable "row" *ONLY* if value = a. If you want it to
stop after row 90 REGARDLESS of ActiveCell.Value, the you need to change how
it detects the row and decides whether to keep going.

Try this:
delete "row = row+1" from its current place
insert "row = row+1" just before "loop until row90"

This will increment "row" every time your code runs, and stop it after row
90.

The end of your code will then be:

activecell.value = \"absent\"

row = row + 1

loop until row 90

end sub

HTH
Ed

"lykwid " wrote in message
...
Huge thanks for your suggestion, but I don't understand what you mean :(


---
Message posted from http://www.ExcelForum.com/

Oh I see what you mean. That isn't what the code does. You said what th
problem was, and then told me how to create the problem :)

Your suggestion means it would only +1 to the row variable if it ha
been added to the other table. The way it is now, I *think* it add
everytime it moves down.

But, this doesn't matter I don't think considering the code runs dow
to in excess of 1,000 rows before I stop it. :(((

Thanks though

--
Message posted from http://www.ExcelForum.com

I tried changing the data type of "Row" from "Integer" to "Byte".

The code ran to row 256 and an error of

"Run-Time Error '6':

Overflow"

appeared. When I debug, the line of code "Row = Row + 1" wa
highlighted. I think this means the method of adding a count to my ro
is incorrect.

Any suggestions?

Thanks :

--
Message posted from http://www.ExcelForum.com

This worked for me.

One thing, if you are checking if the cell contains the letter a, you have
failed to put it in quotes, so it is looking to match the value of the
variable a which has no value, so it will only increment on a blank.

Apparently your computer moves too fast for you to notice that it never
advances after doing the first copy (so it isn't doing exactly what you want
except it doesn't stop - see explanation below). It just continues to copy
from the first location - so you are going past row 90 in the portion
working with cell B98. The problem is that you select currentcell, then
make its value "absent", then go back to the top and ask if activecell = a.
It doesn't, since its value is "absent", so it immediately does the copy
again and repeats this over and over never getting into your first nested
loop so the activecell will advance.

You need to select current cell, make it absent, then dropdown one cell and
then go back to your first nested do.

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
Loop Until row 90
End Sub


"lykwid " wrote in message
...
Oh I see what you mean. That isn't what the code does. You said what the
problem was, and then told me how to create the problem :)

Your suggestion means it would only +1 to the row variable if it had
been added to the other table. The way it is now, I *think* it adds
everytime it moves down.

But, this doesn't matter I don't think considering the code runs down
to in excess of 1,000 rows before I stop it. :(((

Thanks though.


---
Message posted from http://www.ExcelForum.com/

Thanks Tom. It stops at the right place. :)

Except, it's function isn't correct. It adds "Absent" to every cell
instead of only the cells with "a" in. Also, it adds everything withou
"a" to the other table, when I want the reverse.

I will try fiddling with it, however I probably won't be able to and
would be appreciative if you could perhaps work this out also.

I will post if I work it out.

Thanks alot. :

--
Message posted from http://www.ExcelForum.com

Yay! I have it working. Largely with thanks to Tom.

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

--
Message posted from http://www.ExcelForum.com