Sub gives error I don't know how to fix....

D

Dan E

This sub, which gives a "Run-time error '13': Type mismatch", is meant to
put overtime for a 2-week period in the BB column, for all rows that have an
X in the AT column. Hours for the 2 weeks are in AW and AX, and overtime is
anything over 40 for each week. I cannot figure out why this doesn't run -
all help and suggestions gratefully received.

TIA,

Dan
_____________________________
Sub ALL_OT()

Dim oRow As Range
Dim cell As Range

On Error GoTo ws_next4
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
Cells(oRow.Row, "BB").Value = 0
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BB").Value = Cells(oRow.Row, "AW").Value -
40
End If
If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BB").Value = (Cells(oRow.Row, "BB").Value +
_
(Cells(oRow.Row, "AX").Value - 40))
End If
End If
ws_next4:
Next oRow

End Sub
________________________
 
D

Dave Peterson

Just a guess, but I'd look for non-numeric data in the input.

You can change your code to avoid errors by adding a check before you do any
arithmetic:

if isnumeric(Cells(oRow.Row, "AW").Value) then
Cells(oRow.Row, "BB").Value = Cells(oRow.Row, "AW").Value - 40
.....

Sometimes finding that !%!@#ing cell that a user typed in "None" or O (oh, not
zero) is easier.

But if you're going to let others use the macro, you'll want to add the
validation checks. You won't want to be bothered each time they make a typing
mistake.
 
B

Bob Phillips

Dan,

Just got back from a night-out, may not be too clear-headed, but here goes
:).

I tried your code, and with some simple test data that I tried it worked
fine.

What line does it error on, and what are the values in AX and AW at that
point? If you are not sure how to determine, step-through the code by going
into the VB IDE, select the macro, and step the code, F8. Step through each
line until you get the error, at that point when it asks to End or Debug,
click Debug. Then select the statement Cells(oRow.Row, "AW").Value and
Shift-F9 to get a popup with the value. Repeat for the statement
Cells(oRow.row, "AX").Value - 40.

Post back with the findings, in this thread.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dan E

Bob - what happened - the code originally was without 2 or 3 lines:-

[Dim oRow As Range
Dim cell As Range

and

For Each oRow In ActiveSheet.UsedRange.Rows]


, which I later realised needed to be there - and it stalled (without those
lines). Changed the code, after some introspection, to the present form,
and it SEEMED not to run. Did the popup to get the row number, then ran it
again, and it worked, after I'd tried to run it several times WITH the
changes. Debug/troubleshooting is NOT lucidly transparent in VBA....
Anyway, all's well that ends well. To age is to learn, and to inspect code
sloooowly is to fix. Not too clear headed myself - Hardy's Chardonnay takes
ALL the blame.

Cheers, and many thanks,

Dan
 
K

Ken Wright

Sometimes finding that !%!@#ing cell that a user typed in "None" or O (oh,
not
zero) is easier.
Click to expand...

Or the damn spacebar being used to wipe out values - grrrrrrrrrrr
 
K

Ken Wright

Dan just for info. The Dim statements declares to Excel what type of data
that those variables are, and save Excel having to work it out each time.
You can leave them out but there will be an impact on performance, albeit it
could in some cases be so small as to be unnoticeable. Depends on the
routine.

The > For Each oRow In ActiveSheet.UsedRange.Rows] bit is the section of
code that runs through all your rows. the rest of the code then gets
performed against that row, it does what it needs to and then because of
that statement it moves on to the next row. Without that statement it works
on the first row and then stops.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :)
----------------------------------------------------------------------------

Dan E said:
Bob - what happened - the code originally was without 2 or 3 lines:-

[Dim oRow As Range
Dim cell As Range

and

For Each oRow In ActiveSheet.UsedRange.Rows]


, which I later realised needed to be there - and it stalled (without those
lines). Changed the code, after some introspection, to the present form,
and it SEEMED not to run. Did the popup to get the row number, then ran it
again, and it worked, after I'd tried to run it several times WITH the
changes. Debug/troubleshooting is NOT lucidly transparent in VBA....
Anyway, all's well that ends well. To age is to learn, and to inspect code
sloooowly is to fix. Not too clear headed myself - Hardy's Chardonnay takes
ALL the blame.

Cheers, and many thanks,

Dan

Bob Phillips said:
Dan,

Just got back from a night-out, may not be too clear-headed, but here goes
:).

I tried your code, and with some simple test data that I tried it worked
fine.

What line does it error on, and what are the values in AX and AW at that
point? If you are not sure how to determine, step-through the code by
going
into the VB IDE, select the macro, and step the code, F8. Step through
each
line until you get the error, at that point when it asks to End or Debug,
click Debug. Then select the statement Cells(oRow.Row, "AW").Value and
Shift-F9 to get a popup with the value. Repeat for the statement
Cells(oRow.row, "AX").Value - 40.

Post back with the findings, in this thread.

--

HTH

RP
(remove nothere from the email address if mailing direct)


have
an overtime
is
Click to expand...
Click to expand...
 
D

Dan E

Many thanks to all those who have contributed help to me during what has
turned out to be an intense week of trying to fix a set of inherited
spreadsheets that had some problems, while learning enough about VBA to get
the job done ASAP. Thanks to YOUR efforts, Bob Phillips, Ken Wright, Dave
Peterson, Paul B, NC and others, a lot of patients in care facilities are
now less likely to have nursing staff not turn up because they didn't know
they were scheduled, and the patients are therefore less likely to suffer.

Blessings upon all your heads!! :)

Dan E
 
D

Dan E

More thank-yous - to Gord Dibben, Earl Kiosterud, Ron Rosenfeld, among
others. For all your help, thank you so-o-o-o much on behalf of those who
benefited.

Dan
Dan E said:
Many thanks to all those who have contributed help to me during what has
turned out to be an intense week of trying to fix a set of inherited
spreadsheets that had some problems, while learning enough about VBA to
get the job done ASAP. Thanks to YOUR efforts, Bob Phillips, Ken Wright,
Dave Peterson, Paul B, NC and others, a lot of patients in care facilities
are now less likely to have nursing staff not turn up because they didn't
know they were scheduled, and the patients are therefore less likely to
suffer.

Blessings upon all your heads!! :)

Dan E
Click to expand...
 
Top