EN VI

Excel - Program loops through routine one extra time (Return without Gosub)?

How to Excel - Program loops through routine one extra time (Return without Gosub)

Code to enter start times in two adjacent columns, for later calculation. The first half (start time) works correctly, scrolling down until finding first blank and entering current time. The second half, basically the endtime subroutine, enters the correct time, but then loops an extra iteration and throws a "return without gosub" error. EDIT: I understand that I won't be able to control the timing of the entries. I am just trying to get the process down before applying it to a msgbox or similar vehicle.

Sub Macro7()
'
' Macro7 Macro
'
Dim StartValueCell As Integer
StartValueCell = 3

Dim EndValueCell As Integer
EndValueCell = 3

'find first blank cell in start column
Do While Cells(StartValueCell, 2) > 0
    StartValueCell = StartValueCell + 1
Loop

'enter start time in first blank cell
With Cells(StartValueCell, 2)
    .Value = Now
    .NumberFormat = "HH:MM:SS"
End With

'find first blank cell in end column
Do While Cells(EndValueCell, 3) > 0
    EndValueCell = EndValueCell + 1
Loop

GoSub EndTime

EndTime:

'enter end time in end column
With Cells(EndValueCell, 3)
    .Value = Now()
    .NumberFormat = "HH:MM:SS"
End With

Return



'
End Sub

Solution:

You are falling into the "subroutine". You need the following code adjustment:

Do While Cells(EndValueCell, 3) > 0
    EndValueCell = EndValueCell + 1
Loop

GoSub EndTime

Exit Sub   '<------- Add this line

EndTime:

'enter end time in end column
With Cells(EndValueCell, 3)
    .Value = Now()
    .NumberFormat = "HH:MM:SS"
End With

Return

You should really avoid the use of GoSub and call another Sub explicitly as follows:

'find first blank cell in end column
Do While Cells(EndValueCell, 3) > 0
    EndValueCell = EndValueCell + 1
Loop

Call sEndTime(EndValueCell)

End Sub

Private Sub sEndTime(EndValueCell As Long)

  'enter end time in end column
  With Cells(EndValueCell, 3)
      .Value = Now()
      .NumberFormat = "HH:MM:SS"
  End With

End Sub