CODEWITHSUNDEEP
excelvba
MJ98
MJ98

Reputation: 87

Moving to next empty row to fill cell from Userform

I am currently creating a system using VBA for students to choose their classes in university. I have created a userform 'AMForm' for students to do this, where there are adjacent option buttons for each class so they can select 'yes' or 'no' if they want to enroll in the class e.g. "Mechanical engineering" with 'yes' or 'no' option buttons next to it.

Once they submit their form - I want to be able to record their choices on the spreadsheet 'AMChoices'. This spreadsheet has each class in the above headings, so I would like to fill the cell with 'x' if it has been selected e.g. if 'Mechanical Engineering' option button is selected it fills 'x' underneath this cell.

I am doing this for multiple users so I want it to be able to fill the cell, and then when the next user logs their choices, their choices are filled into the next empty row.

Please see below my code.

Set CurrentCell = Range("B2").End(xlDown).Offset(1, 0).Select
If Me.optAMIB1 Then
Total = Total + 20
Worksheets("AMChoices").Range("CurrentCell").Value = "X"
End If

Upvotes: 1

Views: 673

Answers (2)

satya srinivas
satya srinivas

Reputation: 1

I assume that you have a column for serial number or roll number, a few columns for students name and others and then a column for each subject that they have the option for. If that is the case and you need to mark below the heading then you could use the code below 

Dim lastRow as Long
lastRow = Sheet1.Cells( "B" & Rows.Count). End( xlUp ).Offset(1,0).Row

AMChoices.Range("A" & lastRow).Value = <Serial number or Roll number>
AMChoices.Range("B" & lastRow).Value = <Student's Name> 

if Me.optAMIB1 Then
   AMChoices.Range("<Column name of the choice>",lastRow).Value = "X"
End If

You can use the with statement if you want to.

Upvotes: 0

Shai Rado
Shai Rado

Reputation: 33682

First, there's no need to Select the cell you want to add the value "X".

Second, after you set CurrentCell range object, you can directly use it with CurrentCell.Value2 = "X".

Try the code below, more explanations inside the code:

Dim AMChoiceSht As Worksheet
Dim CurrentCell As Range
Dim LastRow As Long

' set the worksheet object
Set AMChoiceSht = ThisWorkbook.Worksheets("AMChoices")

With AMChoiceSht
    LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row ' find last row in column B

    Set CurrentCell = .Range("B" & LastRow + 1) ' set the range of next empty row
End With

If Me.optAMIB1 Then
    Total = Total + 20
    CurrentCell.Value2 = "X"
End If

Upvotes: 1

Related Questions