do until section is not iterating

Question

I have a user form which is for submiting data to excel sheet but everything is working except serial number. it only iterates second time after that it returns same serial number for each entry. I dont know where is the mistake. Please correct this code.

Private Sub cmdSub_Click()
Dim i As Integer
'position cursor in the correct cell A2
Range("A2").Select
i = 1 'set as the first it
'validate first three controls have been entered...
If srv.txtTo.Text = Empty Then 'SRV no. for to
MsgBox "Please Enter SRV NO.To", vbInformation
srv.txtTo.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If

If srv.txtFrom.Text = Empty Then 'SRV no. for to
MsgBox "Please Enter SRV NO.From", vbInformation
srv.txtFrom.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If

If srv.txtLoc.Text = Empty Then 'SRV no. for to
MsgBox "Please Enter SRV NO.To", vbInformation
srv.txtLoc.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If

'if all the above are false (OK) then carry on.
'check to see the next available blank row start at cell A2
Do Until ActiveCell.Value = Empty
    ActiveCell.Offset(1, 0).Select 'move down 1 row
    i = 1 + 1 'keep a count of the ID for later use
Loop

'populate the new data values into the 'test' worksheet.
ActiveCell.Value = i 'next ID Number
ActiveCell.Offset(0, 1).Value = srv.txtTo.Text 'set col B
ActiveCell.Offset(0, 2).Value = srv.txtFrom.Text 'set cl c
ActiveCell.Offset(0, 3).Value = srv.txtLoc.Text 'set col c

'clear down the values ready for the next record entry
srv.txtTo.Text = Empty
srv.txtFrom.Text = Empty
srv.txtLoc.Text = Empty

srv.txtTo.SetFocus ' positions the cursor for next work

End Sub

[INTERESTING READ](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) — Siddharth Rout, Nov 23 '13 at 20:25
Welcome to stackoverflow @amarjeet. If you get a suitable answer please remember to mark it as accepted. To mark an answer as accepted, click on the check mark beside the answer to toggle it from greyed out to filled in. — Reafidy, Nov 24 '13 at 00:08

score 0 · Answer 1 · answered Nov 24 '13 at 00:05

You should forget the loop and use End(xlUp) to get the first available blank cell. I have also changed your method of getting a new ID as your old method could cause duplicates when a row is deleted.

Private Sub cmdSub_Click()

'validate first three controls have been entered...
If srv.txtTo.Text = Empty Then 'SRV no. for to
MsgBox "Please Enter SRV NO.To", vbInformation
srv.txtTo.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If

If srv.txtFrom.Text = Empty Then 'SRV no. for to
MsgBox "Please Enter SRV NO.From", vbInformation
srv.txtFrom.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If

If srv.txtLoc.Text = Empty Then 'SRV no. for to
MsgBox "Please Enter SRV NO.To", vbInformation
srv.txtLoc.SetFocus 'position cursor to try again
Exit Sub 'terminate here - why continue?
End If

'Get the first available blank cell in column A.
With Range("A" & Rows.Count).End(xlUp).Offset(1)
    'populate the new data values into the 'test' worksheet.
    .Value = WorksheetFunction.Max(Range("A:A")) + 1 'next ID Number
    .Offset(0, 1).Value = srv.txtTo.Text 'set col B
    .Offset(0, 2).Value = srv.txtFrom.Text 'set cl c
    .Offset(0, 3).Value = srv.txtLoc.Text 'set col c
End With

'clear down the values ready for the next record entry
srv.txtTo.Text = Empty
srv.txtFrom.Text = Empty
srv.txtLoc.Text = Empty

srv.txtTo.SetFocus ' positions the cursor for next work
End Sub

do until section is not iterating

1 Answers1