#1
|
|||
|
|||
Applying a formula from a checkbox in VBA
Hi,
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:J40").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:J40").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 |
#2
|
|||
|
|||
Enter the formula you want in J4, with J4 the active cell, go to the vba immediate window and enter
Code:
? activecell.FormulaR1C1 |
#3
|
|||
|
|||
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? |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
You're welcome.
|
#6
|
|||
|
|||
Hi NoSparks,
Just wanted to say a HUGE thanks for you're help. It was this little line 'Type:=xlFillDefault' that made all the difference. :-) |
#7
|
|||
|
|||
just a comment
Thanks also NoSparks
I hadnt realised the xlFillDefault was not actually always the default ! But, looking at the thread jap, you puzzle me why are you doing this in VBA? Just use Excel, then the errors are given you in real time. If you want to use it repeatedly then write it in Excel and just load the inputs and read the outputs using VBA Not only is it much easier to debug, it also runs much faster (Excel native calculation is much faster than doing exactly the same thing in VBA) If you set a formula in Excel it "works out what the formula means", saves the result in Excel native language (which takes while) and calculates the answer. You then save it and it recalculates using new input values in the blink of an eye. If you use VBA to set a formula, first it must hand over to the Excel bit (called the "command parser") to work out what your formula means, and then it must calculate the value for an answer, it cant save it because it doesnt know what vba is going to pass it.Probably doesnt make much difference if you are only going to do it once, but anything that is proessing repetitively is going to be slow. Also logical debugging is instantaneous - it will immeditely give you #error, #value etc, rather than having to run your vba through to the suspect point Also testing is easy, you can manually enter all of the posible entries and check that they work every time I see .formulaArray I know it is going to be slow and unreliable, even if you get it working in the first place |
#8
|
||||
|
||||
Quote:
I also don't follow this comment: Quote:
|
Tags |
check box, iferror, index |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Applying styles dynamically? | arkofcovenant | Mail Merge | 1 | 05-08-2015 06:02 AM |
applying a style in a new document | lihin | Word | 1 | 12-13-2012 05:22 AM |
Applying Header to certain pages | chloe.eloise | Word | 2 | 05-16-2011 12:13 AM |
Help applying different margins to different pages | kthomp | Word | 0 | 06-18-2010 11:37 AM |
Applying And Removing Letterhead | skoz55 | Word | 0 | 08-06-2009 10:22 PM |