Quick debugging help needed - TIA

  • Thread starter Thread starter Dan E
  • Start date Start date
D

Dan E

I'm on a 2-hour deadline to get this sub working, and, since I'm new to VBA,
I don't know enough to know which particular very stupid thing I'm doing.
Having said that, if some kind soul can point me in the right direction to
fix this, blessings will be upon your head. This stops in 'week2' with
runtime error 424 - Object required - it's working on a scheduling worksheet
and is looking for overtime hours in particular cases - hence the values 1,
2 or 3 in column W in rows where column AT is X. I know I don't yet know if
number values require "" even.... mutter mutter :-):-

Sub CC_OT()

Dim oRow As Range
Dim cell As Range
On Error GoTo ws_next3
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
If Cells(oRow.Row, "W").Value = "1" Then
week1
ElseIf Cells(oRow.Row, "W").Value = "2" Then
week2
ElseIf Cells(oRow.Row, "W").Value = "3" Then
bothweeks
End If
End If
ws_next3:
Next oRow

End Sub

Sub week1()
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
End If
End Sub

Sub week2()
If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AX").Value - 40
End If
End Sub

Sub bothweeks()
Cells(oRow.Row, "BA").Value = 0
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
End If
If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BA").Value = (Cells(oRow.Row, "BA").Value + _
(Cells(oRow.Row, "AW").Value - 40))
End If
End Sub

TIA

Dan
 
While I'm no guru, I too have problems similar. I switch to VB editor
at error time, and use "immediate window" to type in debug statements.
Typically you can isolate problem very quickly.

If there was no "object" most likely something is wrong with your
variable oRow, so type into immediate window
? oRow.address
and see what pops up.
 
While I'm no guru, I too have problems similar. I switch to VB editor
at error time, and use "immediate window" to type in debug statements.
Typically you can isolate problem very quickly.

If there was no "object" most likely something is wrong with your
variable oRow, so type into immediate window
? oRow.address
and see what pops up.
 
Thanks for the suggestion, which just gives the same error message.
Learning is hard when there's time pressure to solve a particular problem
and one doesn't even know the syntax! :-)

Thanks again,

Dan
 
CC_OT knows what oRow is. It was declared in that procedure.

week1 (and week2 and week3) have no idea what it refers to.

One way around it is to have the called procedure expect some kind of
information that should be passed to it. And then have the calling procedure
pass that info:

Option Explicit

Sub CC_OT()

Dim oRow As Range
Dim cell As Range

On Error GoTo ws_next3
For Each oRow In ActiveSheet.UsedRange.Rows
If Cells(oRow.Row, "AT").Value = "X" Then
If Cells(oRow.Row, "W").Value = "1" Then
Call week1(oRow)
ElseIf Cells(oRow.Row, "W").Value = "2" Then
Call week2(oRow)
ElseIf Cells(oRow.Row, "W").Value = "3" Then
Call bothweeks(oRow)
End If
End If
ws_next3:
Next oRow

End Sub
Sub week1(oRow As Range)
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
End If
End Sub

Sub week2(oRow As Range)
If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AX").Value - 40
End If
End Sub

Sub bothweeks(oRow As Range)
Cells(oRow.Row, "BA").Value = 0
If Cells(oRow.Row, "AW").Value > 40 Then
Cells(oRow.Row, "BA").Value = Cells(oRow.Row, "AW").Value - 40
End If
If Cells(oRow.Row, "AX").Value > 40 Then
Cells(oRow.Row, "BA").Value = (Cells(oRow.Row, "BA").Value + _
(Cells(oRow.Row, "AW").Value - 40))
End If
End Sub

============
I think if I were doing this, I'd pass just the row number. But it looked like
it would be simpler (typing only!) to pass the oRow range object.
 
Many many thanks, Dave - worked like a charm and saved my bacon! Oh for a
VBA general and syntax guide/tutorial written for simple physicists...

Thanks again, and to Bob Phillips for his huge contribution,

Dan E
 
Hmmmm. Simple physicists. (Jumbo shrimp, Swedish chef...)

Glad you got it working.

Dan said:
Many many thanks, Dave - worked like a charm and saved my bacon! Oh for a
VBA general and syntax guide/tutorial written for simple physicists...

Thanks again, and to Bob Phillips for his huge contribution,

Dan E
Click to expand...
 
Jumbo shrimp I understand, but Sweden DOES have cuisine (though I loved the
Swedish chef in the Muppets...) ( as well as stunning crystal chandeliers)
:-)

Physics PhD is from '65, practically pre-Newtonian.... Plaque invades the
brain... aaaarghhhhhh!!!!!

Dan
 
Dave,

:)

Actually, my response was a bit of a non-sequitur, I later realized. You
were mentioning oxymorons, self-contradicting things. My Alfredo sauce
wasn't that, it's a redundancy, like pin number.
 
Back
Top