Checkbox Perform IfError Index Function

[jap7675]

Hello Everyone,

I've been struggling with this for a while now, so i realllyyyy hope someone can help!

I have this code

Private Sub CheckBox1_Click()
If OptionButton1.Value = True Then
Sheets("Sheet1").Select
Range("J4").Select
Selection.FormulaArray = "=IFERROR(INDEX(C$2:C$14, SMALL(IF($B$2:$B$14=1, ROW($B$2:$B$14)-1),ROWS(G$2:G2))),"")"
Selection.Columns.AutoFit

ElseIf OptionButton1.Value = False Then
Range("J4").Value = " "
End If

End Sub

Where I'm trying to get the formula perforned in cell J4. It just wont do it!
And I dont really get why!

Please Help

 

[portews]

In general, you don't need to select cells to do things with them in VBA. The macro will run faster if you don't have to select cells.

The paste formula method should work for you. Try this:

[COLOR=#333333]Private Sub CheckBox1_Click()[/COLOR]
'http://www.mrexcel.com/forum/excel-questions/903173-checkbox-perform-iferror-index-function.html
If OptionButton1.Value = True Then
    Worksheets("Sheet1").Range("J4").Formula = "=IFERROR(INDEX(C$2:C$14, SMALL(IF($B$2:$B$14=1, ROW($B$2:$B$14)-1),ROWS(G$2:G2))),"""")"
Else
    Range("J4").Value = ""
End If
End Sub

 

[jap7675]

Hi, Thanks so much for your hel - im getting really close to solving my problem.



Private Sub CheckBox1_Click()

If CheckBox1.Value = True Then
Sheets("Sheet1").Range("J4:J40").FormulaArray = "=IFERROR(INDEX(C$2:C$14, SMALL(IF($B$2:$B$14=1, ROW($B$2:$B$14)-1),ROWS(G$2:G2))),"""")"
Sheets("Sheet1").Range("J4:J40").Columns().AutoFit

ElseIf CheckBox1.Value = False Then
Range("J4:J40").Value = " "
End If

End Sub



I just need the (G$2:G2) to occur sequentially {e.g. ((G$2:G3), (G$2:G4)), (G$2:G5)) etc}when the formula is repeated across the rane. Is this possible?