1

I'm trying to make this user form output option button results at the end of a column. Originally I tried using the same i in the for next loop that I have in my main module, but the user form wouldn't recognize the variable, even after dimensioning it as public. Any ideas?

Private Sub cmdOk_Click()

If opt1 Then
    Range(Range(“E3”), Range(“E3”).End(xlDown)) = "None"
ElseIf opt2 Then
    Range(Range(“E3”), Range(“E3”).End(xlDown)) = "3/8 Plain"
ElseIf opt3 Then
    Range(Range(“E3”), Range(“E3”).End(xlDown)) = "1/2 Plain"
ElseIf opt4 Then
    Range(Range(“E3”), Range(“E3”).End(xlDown)) = "1/2 Herringbone"
ElseIf opt5 Then
    Range(Range(“E3”), Range(“E3”).End(xlDown)) = "1/2 Diamond"
End If

Unload frmLagging

End Sub
FruitUser
  • 105
  • 1
  • 2
  • 9

2 Answers2

1

You need to find the last row in that column and then write to it. Here is an example (Untested). You can read more about finding the last row Here

Dim ws As Worksheet
Dim lRow As Long
Dim rng As Range

Set ws = ThisWorkbook.Sheets("Sheet1")

With ws
    '~~> We add 1 so that we get the next empty cell
    lRow = .Range("E" & .Rows.Count).End(xlUp).Row + 1

    Set rng = .Range("E" & lRow)

    If opt1 Then
        rng.Value = "None"
    ElseIf opt2 Then
        rng.Value = "3/8 Plain"
    ElseIf opt3 Then
        rng.Value = "1/2 Plain"
    ElseIf opt4 Then
        rng.Value = "1/2 Herringbone"
    ElseIf opt5 Then
        rng.Value = "1/2 Diamond"
    End If
End With
Unload frmLagging
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
0

Also, Here is one of my favorite iif statements,

Private Sub CommandButton1_Click()
    Dim Rws As Long, Rng As Range, x
    Rws = Cells(Rows.Count, "E").End(xlUp).Row

    Set Rng = Cells(Rws + 1, "E")

    x = IIf(Opt1, "None", IIf(Opt2, "3/8 Plain", IIf(Opt3, "1/2 Plain", IIf(Opt4, "1/2 Herringbone", IIf(Opt5, "1/2 Diamond", "Nada")))))
    Rng = x

    Unload Me
End Sub
Davesexcel
  • 6,896
  • 2
  • 27
  • 42