[VBA] trying to make function for option group

[Srbin do jaja]

here is the code i have and it works as a charm

Private Sub CommandButton2_Click()
    Dim LastRow As Object
    Dim totalRows As Integer
    Dim i As Integer
    Dim x As Integer

    Set LastRow = Sheet2.Range("B65536").End(xlUp)

    If OptionButton1.Value = True Then
        LastRow.Offset(1, 1).Value = OptionButton1.Caption
    ElseIf OptionButton2.Value = True Then
        LastRow.Offset(1, 1).Value = OptionButton2.Caption
    ElseIf OptionButton1.Value = False And OptionButton2.Value = False Then
        MsgBox "Answer the question: Kog ste pola"
        Exit Sub
    End If
End Sub

but i have 10 option groups with 4 option button in each i was trying to make a function out of above example to:

Private Sub getOptBtnValue()

    x = 0
    For i = 12 To 16
    x = x + 1
    If Controls("OptionButton" & i) = True Then
        LastRow.Offset(1, 4).Value = Controls("OptionButton" & i).Caption
           Exit For
    End If
  
    If x = 5 Then
       MsgBox "Answer the question: Koliko ukupno imate radnog iskustva?"
       Exit Sub
    End If
    
    Next i
    
End Sub

when i run this function it gives me an error in line where

LastRow.Offset(1, 4).Value = Controls("OptionButton" & i).Caption

what am i doing wrong?

 

[Rick Rothstein]

The first code you posted has this line in it...

Set LastRow = Sheet2.Range("B65536").End(xlUp)

You do not have it in your own code, but your code still refers to the LastRow cell.

 

[Srbin do jaja]

The first code you posted has this line in it...

Set LastRow = Sheet2.Range("B65536").End(xlUp)

You do not have it in your own code, but your code still refers to the LastRow cell.

i forgot to say that after this

Private Sub CommandButton2_Click()
    Dim LastRow As Object
    Dim totalRows As Integer
    Dim i As Integer
    Dim x As Integer

    Set LastRow = Sheet2.Range("B65536").End(xlUp)

i call sub getOptBtnValue

 

[Rick Rothstein]

But getOptBtnValue doesn't know anything about LastRow because it is declared inside the CommandButton2_Click event procedure. Where is the getOptBtnValue code located... in the sheet module with the CommandButton2 Click event code or in a standard module (where macros are installed)?

 

[Srbin do jaja]

well i'm novice in VBA programming, where should i declare getOptBtnValue. i'm super confused. right now its in function in side sheet2.

Private Sub CommandButton2_Click()
    Dim LastRow As Object
    Dim totalRows As Integer
    Dim i As Integer
    Dim x As Integer

    Set LastRow = Sheet2.Range("B65536").End(xlUp)

    If OptionButton1.Value = True Then
        LastRow.Offset(1, 1).Value = OptionButton1.Caption
    ElseIf OptionButton2.Value = True Then
        LastRow.Offset(1, 1).Value = OptionButton2.Caption
    ElseIf OptionButton1.Value = False And OptionButton2.Value = False Then
        MsgBox "Answer the question: Kog ste pola"
        Exit Sub
    End If
End Sub

this works. since i have 5 more opt groups i dont want to write same code for every opt group. what would be a solution?