error438: Object doesn't support this property or method (OptionButtons in a Range)

[vywes]

Small	Medium	Large
Option Button 1	Option Button 2	Option Button 3
Option Button 4	Option Button 5	Option Button 6

Using the group box, option button 1-3 is grouped as one group; 4-6 as another; and the next rows follow the same trend.

I'm trying to write the VBA so that in range F2:H5, for each row, if the Option Button in the left column is checked then cell.Offset(0,4).value=[a formula]; if option button in the middle column is checked then cell.Offset(0,3).value=[another formula]; if option button in the right column is checked then cell.Offset(0,2).value = [another formula]

I'm fairly new to VBA so am trying to piece together what I can and have come up with this so far:

Sub ServingSize()

Dim rng As Range, cell As Range
Set rng = Range("H14:J16")

Dim rng1 As Range, cell1 As Range
Set rng1 = Range("H14:H16")

Dim rng2 As Range, cell2 As Range
Set rng2 = Range("I14:I16")

Dim rng3 As Range, cell3 As Range
Set rng3 = Range("J14:J16")

For Each cell In rng
If Range("rng1").cell1.OptionButton = True Then
cell.Offset(0, 4).Value = cell.Offset(0, 3).Value * 0.5
ElseIf Range("rng2").cell2.OptionButton = True Then
cell.Offset(0, 3).Value = cell.Offset(0, 2).Value
ElseIf Range("rng3").cell3.OptionButton = True Then
cell.Offset(0, 2).Value = cell.Offset(0, 1).Value * 1.5
End If
Next cell

End Sub

What did I do wrong here?

 

[Micron]

See posts here (esp. 3,6,14,15). Maybe that thread will guide you.

Reset option (radio) buttons only for one row

I have a macro to reset all of my option buttons on a sheet using Sheets("Survey1").OptionButtons = False This works great, but I also need to be able to reset option buttons on only a selected row. I'll have a 'reset' button on each row that will reset the buttons for that row. Thanks!

www.mrexcel.com