Need direction on Code

[William D. Lee]

Team,

I am writing code for a User Form. I have an error in my code which will not allow me to start logging at A2 and moving down 1 row everytime the form is filled out and submitted. I am going to take some courses from this website, but it would be greatly appreciate it if someone could provide guidance on this matter. Thanks ahead of time.

Private Sub cmdFormCancel_Click()
Unload Me
End Sub

Private Sub cmdFormSubmit_Click()

Sheet1.Activate

Range("A2:K2").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select


ActiveCell.Offset(0, -0).Value = TextBox1.Value
ActiveCell.Offset(0, 1).Value = TextBox2.Value
ActiveCell.Offset(0, 2).Value = TextBox3.Value
ActiveCell.Offset(0, 3).Value = OptionButton1.Value
ActiveCell.Offset(0, 3).Value = OptionButton2.Value
ActiveCell.Offset(0, 4).Value = TextBox4.Value
ActiveCell.Offset(0, 5).Value = TextBox5.Value
ActiveCell.Offset(0, 6).Value = TextBox6.Value
ActiveCell.Offset(0, 7).Value = OptionButton3.Value
ActiveCell.Offset(0, 7).Value = OptionButton4.Value
ActiveCell.Offset(0, 8).Value = TextBox7.Value
ActiveCell.Offset(0, 9).Value = OptionButton5.Value
ActiveCell.Offset(0, 9).Value = OptionButton6.Value
ActiveCell.Offset(0, 10).Value = TextBox8.Value

If OptionButton1.Value = True Then
ActiveCell.Offset(0, 3).Value = "Yes"

End If

If OptionButton2.Value = True Then
ActiveCell.Offset(0, 3).Value = "No"

End If

If OptionButton3.Value = True Then
ActiveCell.Offset(0, 7).Value = "Yes"

End If

If OptionButton4.Value = True Then
ActiveCell.Offset(0, 7).Value = "No"

End If

If OptionButton5.Value = True Then
ActiveCell.Offset(0, 9).Value = "Yes"

End If

If OptionButton6.Value = True Then
ActiveCell.Offset(0, 9).Value = "No"

End If

Unload Me



End Sub


William D. Lee

 

[shrivallabha]

Hi,

I am not sure about the rest of the code. You could change the code syntax like below. Make sure you adjust the cell references correctly. And please use CODE tags for posting VBA code.

Private Sub cmdFormSubmit_Click()
Dim lngLastRow As Long

Sheet1.Activate
lngLastRow = Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row

'\\ Please check and adjust these columns
Cells(lngLastRow, 1).Value = TextBox1.Value
Cells(lngLastRow, 1).Value = TextBox2.Value
Cells(lngLastRow, 2).Value = TextBox3.Value
Cells(lngLastRow, 3).Value = OptionButton1.Value
Cells(lngLastRow, 3).Value = OptionButton2.Value
Cells(lngLastRow, 4).Value = TextBox4.Value
Cells(lngLastRow, 5).Value = TextBox5.Value
Cells(lngLastRow, 6).Value = TextBox6.Value
Cells(lngLastRow, 7).Value = OptionButton3.Value
Cells(lngLastRow, 7).Value = OptionButton4.Value
Cells(lngLastRow, 8).Value = TextBox7.Value
Cells(lngLastRow, 9).Value = OptionButton5.Value
Cells(lngLastRow, 9).Value = OptionButton6.Value
Cells(lngLastRow, 10).Value = TextBox8.Value

If OptionButton1.Value = True Then
  Cells(lngLastRow, 3).Value = "Yes"
End If

If OptionButton2.Value = True Then
  Cells(lngLastRow, 3).Value = "No"
End If

If OptionButton3.Value = True Then
  Cells(lngLastRow, 7).Value = "Yes"
End If

If OptionButton4.Value = True Then
  Cells(lngLastRow, 7).Value = "No"
End If

If OptionButton5.Value = True Then
  Cells(lngLastRow, 9).Value = "Yes"
End If

If OptionButton6.Value = True Then
  Cells(lngLastRow, 9).Value = "No"
End If

Unload Me

End Sub