Thread: Applying a formula from a checkbox in VBA
View Single Post
 
Old 11-19-2015, 08:30 AM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
  
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default
Here you go and have a read of this.
Code:
Private Sub CheckBox1_Click()
If OptionButton1.Value = True Then
    With Sheets("Sheet1").Range("J4")
        .FormulaArray = "=IFERROR(INDEX(R2C[-7]:R14C[-7], SMALL(IF(R2C2:R14C2=1, ROW(R2C2:R14C2)-1),ROWS(R2C[-3]:R[-2]C[-3]))),"""")"
        .AutoFill Destination:=Range("J4:J40"), Type:=xlFillDefault
    End With
Else
    Sheets("Sheet1").Range("J4:J40").Value = " "
End If
End Sub
Reply With Quote