Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

GoTo statement/Object Req'd 2

Status
Not open for further replies.
stillwillyboy

stillwillyboy

Technical User
Jan 22, 2004
165
US
I am using Input Boxes for entering the employee number, job number, hours and units (if units are needed) onto a sheet. I’m trying to use a GoTo statement to skip over the entry of units if the job is straight time, as opposed to piece work. When entering the employee number, job, hours and units (if units are needed), I should end up with the following:

EmplNumb Job Hours Units
1111 111 100 100
1111 181 100
1111 999 200 200
1111 19965 200

Note that the jobs 181 and 19965 do not have units. This is b/c they are straight time.

Below is my code for the hours entry. If the job needs units, then the proc should flow to the next Do for the entering of units. If the job does not have units, then I want to go back to the first Input box for a new employee number to start the process over.

Do ‘hours entry part of code
ValidEntry3 = False ' 'resets ValidEntry3 to false for each loop thru
TSEntry3 = InputBox("Enter the Hours")
If UCase(TSEntry3) = "EXIT" Then
MsgBox "You have decided to exit."
TSEntry1 = "" And TSEntry2 = "" And TSEntry3 = "" ‘clears any other info
Exit Sub
Else
If IsNumeric(TSEntry3) And TSEntry3 >= 10 And TSEntry3 <= 999 Then
ValidEntry3 = True
Else
MsgBox "Invalid Number of Hours. "
TSEntry3 = ""
End If
End If
Range("A65536").End(xlUp).Offset(0, 2) = TSEntry3
Loop Until ValidEntry3 = True

‘the above works as desired

'----the following is for skipping the units cell for straight time

With ActiveCell
If ActiveCell.Offset(0, 1).Value = 180 Or _
ActiveCell.Offset(0, 1).Value = 181 Or _
ActiveCell.Offset(0, 1).Value = 182 Or _
ActiveCell.Offset(0, 1).Value = 183 Or _
ActiveCell.Offset(0, 1).Value = 184 Or _
ActiveCell.Offset(0, 1).Value = 185 Or _
ActiveCell.Offset(0, 1).Value = 186 Or _
ActiveCell.Offset(0, 1).Value = 187 Or _
ActiveCell.Offset(0, 1).Value = 188 Or _
ActiveCell.Offset(0, 1).Value = 189 Or _
ActiveCell.Offset(0, 1).Value = 190 Or _
ActiveCell.Offset(0, 1).Value = 191 Or _
Right(ActiveCell, 2).Offset(0, 1).Value = 21 Or _
Right(ActiveCell, 2).Offset(0, 1).Value = 50 Or _
Right(ActiveCell, 2).Offset(0, 1).Value = 65 Or _
Right(ActiveCell, 2).Offset(0, 1).Value = 95 Or _
Right(ActiveCell, 2).Offset(0, 1).Value = 97 Then GoTo GoBacktoClientNumber
'End If
End With

‘The problem is with the Object Required error message. Isn’t the activecell my object?

If I change the line with 97 to:
Right(ActiveCell, 2).Offset(0, 1).Value = 97 Then
GoTo GoBacktoClientNumber
End if
End with

I then get the Object Req error after entering the hours.

Also, is there an easier way to do this than using the GoTo statement?

TIA
Bill
 
Sort by date Sort by votes
Get rid of the following 2 lines:
With ActiveCell
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Bill,

Substitute the following for all of your IF ... OR ... THEN stuff (including the With Activecell):
Code: 
   EmpNumSuffix = CInt(Right$(ActiveCell.Offset(0, 1).Text, 2))
   
   If (ActiveCell.Offset(0, 1).Value >= 180 And _
       ActiveCell.Offset(0, 1).Value <= 191) Or _
       EmpNumSuffix = 21 Or _
       EmpNumSuffix = 50 Or _
       EmpNumSuffix = 65 Or _
       EmpNumSuffix = 95 Or _
       EmpNumSuffix = 97 Then GoTo GoBacktoClientNumber
I DIM'd EmpNumSuffix as an integer.

There are almost no circumstances where GoTo is required (On Error GoTo being an important exception) so I'm sure it can be eliminated. I just can't follow all of your logic flow since I don't see the GoBacktoClientNumber label. If you can fill in the blanks, I'm sure the code can be streamlined a bit.


Regards,
Mike
 
Upvote 0 Downvote
PHV, that works with some of the numbers, but not with the following:

If Right(ActiveCell, 2).Offset(0, 1).Value = 21 Or _
Right(ActiveCell, 2).Offset(0, 1).Value = 50 Or _
Right(ActiveCell, 2).Offset(0, 1).Value = 65 Or _
Right(ActiveCell, 2).Offset(0, 1).Value = 95 Or _
Right(ActiveCell, 2).Offset(0, 1).Value = 97 Then
GoTo GoBacktoClientNumber
End If


As always,

Thanks

Bill
 
Upvote 0 Downvote
Mike,

Thanks. I'll give it a try. I agree with not being able to follow my logic. My coding skills are not the best to say the least and I have to sometimes take the long way around the yard to get to the door.

Bill
 
Upvote 0 Downvote
If Right(ActiveCell[!].[/!]Offset(0, 1).Value[!], 2)[/!] = 21 Or _
...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Bill said:
My coding skills are not the best to say the least and I have to sometimes take the long way around the yard to get to the door.
Click to expand...
If you stay out of the divots you might see some interesting things that way! [tongue]


Regards,
Mike
 
Upvote 0 Downvote
I‘ve come to the conclusion that I’m probably trying to re-invent the wheel with this code. But I am still trying to get it to work. I need to be able to enter timesheets, some with piece work, some with only straight time. If straight time, then skip the Units Input box and start over, else, go to the Units box and enter units, then start over.

Sub EnterTimesheetInfoSkipUnitsForStraightTime()
Application.StatusBar = "Entering Timesheet Information"
Application.ScreenUpdating = False
Dim TSEntry1, TSEntry2, TSEntry3, TSEntry4, TSEntry5 As Single
Dim JobNumSuffix As Single
Worksheets("TSEntryTest").Activate
Do
With Selection 'selects first empty cell in col A
Range("A65536").End(xlUp).Offset(1, 0).Select
Do
ValidEntry1 = False 'resets ValidEntry1 to false for each loop thru
TSEntry1 = InputBox("Enter the Client Number. If you want to exit, then type EXIT and select OK.")
If UCase(TSEntry1) = "EXIT" Then
MsgBox "You have decided to exit."
TSEntry1 = "" 'clears cell when exiting at thsi point
Exit Sub
Else
If IsNumeric(TSEntry1) And TSEntry1 >= 1000 _
And TSEntry1 <= 2999 Then
ValidEntry1 = True
Else
MsgBox "Invalid Client Number. Either not 4 digits or " _
& "not in range between 1000 and 2999."
'TSEntry1 = "" 'clears cell for new info
End If
End If
Range("A65536").End(xlUp).Offset(1, 0) = TSEntry1
Loop Until ValidEntry1 = True
'--------------------
Do
ValidEntry2 = False 'resets ValidEntry2 to false for each loop thru
TSEntry2 = InputBox("Enter the Job and Step Number")
If UCase(TSEntry2) = "EXIT" Then
MsgBox "You have decided to exit."
TSEntry1 = "" And TSEntry2 = ""
Exit Sub
Else
If IsNumeric(TSEntry1) And TSEntry1 >= 180 And JEntry1 <= 999999999 Then
ValidEntry2 = True
Else
MsgBox "Invalid Job Number Number. "
TSEntry2 = ""
End If
End If
Range("A65536").End(xlUp).Offset(0, 1) = TSEntry2
Loop Until ValidEntry2 = True
'-----------------------
Do
ValidEntry3 = False ' 'resets ValidEntry3 to false for each loop thru
TSEntry3 = InputBox("Enter the Hours")
If UCase(TSEntry3) = "EXIT" Then
MsgBox "You have decided to exit."
TSEntry1 = "" And TSEntry2 = "" And TSEntry3 = ""
Exit Sub
Else
If IsNumeric(TSEntry3) And TSEntry3 >= 10 And TSEntry3 <= 999 Then
ValidEntry3 = True
Else
MsgBox "Invalid Number of Hours. "
TSEntry3 = ""
End If
'------------------
‘the problems begin here. The Else statement causes a Do _ w/o a Loop message. There are some issues with not _ placing the GoTo statement on the same line with the _
Then statement. I have tried to use code from both Mike _
and PHV
If (ActiveCell.Offset(0, 1).Value >= 180 And _
ActiveCell.Offset(0, 1).Value <= 191) Or _
Right(ActiveCell.Offset(0, 1).Value, 2) = 21 Or _
Right(ActiveCell.Offset(0, 1).Value, 2) = 50 Or _
Right(ActiveCell.Offset(0, 1).Value, 2) = 65 Or _
Right(ActiveCell.Offset(0, 1).Value, 2) = 95 Or _
Right(ActiveCell.Offset(0, 1).Value, 2) = 97 Then _
Range("A65536").End(xlUp).Offset(0, 2) = TSEntry3
'GoTo GoBacktoClientNumber
'Else
Range("A65536").End(xlUp).Offset(0, 2) = TSEntry3
End If

Range("A65536").End(xlUp).Offset(0, 2) = TSEntry3
Loop Until ValidEntry3 = True
'-----------------------------------
Do
ValidEntry4 = False ' 'resets ValidEntry4 to false for each loop thru
TSEntry4 = InputBox("Enter the Units, If Required")
If UCase(TSEntry4) = "EXIT" Then
MsgBox "You have decided to exit."
TSEntry1 = "" And TSEntry2 = "" And TSEntry3 = "" And TSEntry4 = ""
Exit Sub
Else
If IsNumeric(TSEntry4) And TSEntry4 >= 1 And TSEntry4 <= 99999 Then
ValidEntry4 = True
Else
MsgBox "Possible Invalid Number of Units. Check Timesheet " _
& "or Call Shop For Correct Amount."
TSEntry4 = ""
End If
End If
Range("A65536").End(xlUp).Offset(0, 3) = TSEntry4
Loop Until ValidEntry4 = True
End With
GoBacktoClientNumber:
Loop Until UCase(TSEntry1) = "exit"
End Sub

Any help will be grately appreciated.

Thanks,

Bill
 
Upvote 0 Downvote
The culprit is highlighted:
If (ActiveCell.Offset(0, 1).Value >= 180 And _
...
Right(ActiveCell.Offset(0, 1).Value, 2) = 97 Then [highlight]_[/highlight]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
If I remove the underscore, then I get the Loop w/o a Do message. If I leave the underscore, the procedure starts, but does not function properly. If I leave the underscore and remove the ‘ from Else, then I get Else w/o If, even tho there is an If.
 
Upvote 0 Downvote
Do
ValidEntry3 = False ' 'resets ValidEntry3 to false for each loop thru
TSEntry3 = InputBox("Enter the Hours")
If UCase(TSEntry3) = "EXIT" Then
MsgBox "You have decided to exit."
TSEntry1 = "" And TSEntry2 = "" And TSEntry3 = ""
Exit Sub
[highlight]End If[/highlight]
If IsNumeric(TSEntry3) And TSEntry3 >= 10 And TSEntry3 <= 999 Then
ValidEntry3 = True
Else
MsgBox "Invalid Number of Hours. "
TSEntry3 = ""
End If
If (ActiveCell.Offset(0, 1).Value >= 180 And _
ActiveCell.Offset(0, 1).Value <= 191) Or _
Right(ActiveCell.Offset(0, 1).Value, 2) = 21 Or _
Right(ActiveCell.Offset(0, 1).Value, 2) = 50 Or _
Right(ActiveCell.Offset(0, 1).Value, 2) = 65 Or _
Right(ActiveCell.Offset(0, 1).Value, 2) = 95 Or _
Right(ActiveCell.Offset(0, 1).Value, 2) = 97 Then[highlight] [/highlight]
Range("A65536").End(xlUp).Offset(0, 2) = TSEntry3
GoTo GoBacktoClientNumber
Else
Range("A65536").End(xlUp).Offset(0, 2) = TSEntry3
End If
Range("A65536").End(xlUp).Offset(0, 2) = TSEntry3
Loop Until ValidEntry3 = True

Tip: try to indent your code.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
1K
morechocolate
morechocolate
remeng
  • Locked
  • Question
GOTO inside a CALL Excel VBA
Replies
6
Views
197
mintjulep
M
remeng
  • Locked
  • Question
Goto Shape name in powerpoint via vba
Replies
2
Views
217
SkipVought
S
DylaBrion
  • Locked
  • Question
Excel VB - Object Reference not set to an instance of the object?
Replies
18
Views
492
DylaBrion
DylaBrion

Part and Inventory Search

Sponsor

Back
Top