DharmeshKP
New Member
- Joined
- Feb 15, 2020
- Messages
- 34
- Office Version
- 2007
- Platform
- Windows
I have user form for patient data entry
I wish to enter data in next available row if it is not entered in current date
If entry exist for same Reg no in current date then I wish to update with userform details
Below is my code
it show error "next without for"
Please guide me to solve this problem
I wish to enter data in next available row if it is not entered in current date
If entry exist for same Reg no in current date then I wish to update with userform details
Below is my code
it show error "next without for"
Please guide me to solve this problem
VBA Code:
Private Sub CommandButton2_Click()
Dim x As Long
Dim y As Worksheet
Dim c As Range
Dim TDate As Long
Set y = Sheets("Daily")
x = y.Range("A" & Rows.Count).End(xlUp).Row
lrow = y.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row
For i = 1 To lrow
'search for today's date
If InStr(Format(y.Cells(i, "I"), "dd/mm/yyyy"), Format(Date, "dd/mm/yyyy")) Then TDate = i: Exit For
Next i
If TDate <> 0 Then
For j = TDate To lrow
'search name starting from today's date row
If Me.TextBox1.Value = y.Cells(j, 1) Then
y.Cells(y, 1).Value = TextBox1.Text
y.Cells(y, 2).Value = TextBox2.Text
y.Cells(y, 3).Value = TextBox3.Text
y.Cells(y, 4).Value = TextBox4.Text
y.Cells(y, 5).Value = TextBox5.Text
y.Cells(y, 6).Value = TextBox7.Text
y.Cells(y, 7).Value = TextBox8.Text
y.Cells(y, 8).Value = TextBox9.Text
Next j
End If
With y
.Cells(x + 1, "A").Value = TextBox1.Text
.Cells(x + 1, "B").Value = TextBox2.Text
.Cells(x + 1, "C").Value = TextBox3.Text
.Cells(x + 1, "D").Value = TextBox4.Text
.Cells(x + 1, "E").Value = TextBox5.Text
.Cells(x + 1, "F").Value = TextBox6.Text
.Cells(x + 1, "G").Value = TextBox7.Text
.Cells(x + 1, "H").Value = TextBox8.Text
.Cells(x + 1, "I").Value = [now()]
.Cells(x + 1, "J").Value = Application.UserName
End With
'clear the data
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
ActiveWorkbook.Save
End Sub