Option Button Macro

[aja4481]

Hello all. i'm trying to set up a macro where if someone chooses the option button, Column B is subtracted from Column A. But if the button is unchecked, Column A is the whole number.

if anyone can help me with how I can set this up I would appreciate it.

Thank you!

 

[Jaye7]

The following will subtract column B from column A and put the value into column C.

Try it on a test page just in case, better safe than sorry.

Private Sub Optionbutton1_click()
 
Range("B2").Select
Cells(65535, ActiveCell.Column).Select
   Range(Selection, Selection.End(xlUp)).Select
   ActiveCell.Select
   ActiveCell.Offset(0, 1).Select
Set BottomCell = ActiveCell
    Set TopCell = Cells(1, ActiveCell.Column)
      Range(TopCell, BottomCell).Select
Dim Cell As Range
For Each Cell In Selection
  If Cell.Value = "" Then
    ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
   ActiveCell.Offset(1, 0).Select
  End If
Next Cell
Me.Optionbutton1.Value = False
End Sub

 

[aja4481]

thank you very much! it works great. is it possible to have either the amount only included or excluded just from Column A and not put the revised value in colmn C?

So checked Column A = A-B; Unchecked A remins the same

 

[Jaye7]

Try the following, it worked on my version.

Private Sub Optionbutton1_click()
Me.Optionbutton1.Value = False
 
Range("B2").Select
Cells(65535, ActiveCell.Column).Select
   Range(Selection, Selection.End(xlUp)).Select
   ActiveCell.Select
   ActiveCell.Offset(0, -1).Select
Set BottomCell = ActiveCell
    Set TopCell = Cells(1, ActiveCell.Column)
      Range(TopCell, BottomCell).Select
 
Dim Cell As Range
For Each Cell In Selection
  If Cell.Value <> "" Then
    ActiveCell.FormulaR1C1 = ActiveCell.Value - ActiveCell.Offset(0, 1)
   ActiveCell.Offset(1, 0).Select
  End If
   '
Next Cell
End Sub