Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-18-2015, 02:28 PM
jap7675 jap7675 is offline Applying a formula from a checkbox in VBA Windows 8 Applying a formula from a checkbox in VBA Office 2007
Novice
Applying a formula from a checkbox in VBA
 
Join Date: Nov 2015
Posts: 6
jap7675 is on a distinguished road
Default 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
Reply With Quote
  #2  
Old 11-18-2015, 10:46 PM
NoSparks NoSparks is offline Applying a formula from a checkbox in VBA Windows 7 64bit Applying a formula from a checkbox in VBA 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

Enter the formula you want in J4, with J4 the active cell, go to the vba immediate window and enter
Code:
? activecell.FormulaR1C1
hit enter and you'll see what the code is for that formula, except that the quotes need doubled up, have your code put this into J4 only, then use .autofill to copy down as necessary.
Reply With Quote
  #3  
Old 11-19-2015, 12:59 AM
jap7675 jap7675 is offline Applying a formula from a checkbox in VBA Windows 8 Applying a formula from a checkbox in VBA Office 2007
Novice
Applying a formula from a checkbox in VBA
 
Join Date: Nov 2015
Posts: 6
jap7675 is on a distinguished road
Default

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?
Reply With Quote
  #4  
Old 11-19-2015, 08:30 AM
NoSparks NoSparks is offline Applying a formula from a checkbox in VBA Windows 7 64bit Applying a formula from a checkbox in VBA 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
  #5  
Old 11-20-2015, 07:02 AM
NoSparks NoSparks is offline Applying a formula from a checkbox in VBA Windows 7 64bit Applying a formula from a checkbox in VBA 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

You're welcome.
Reply With Quote
  #6  
Old 11-23-2015, 03:38 AM
jap7675 jap7675 is offline Applying a formula from a checkbox in VBA Windows 8 Applying a formula from a checkbox in VBA Office 2007
Novice
Applying a formula from a checkbox in VBA
 
Join Date: Nov 2015
Posts: 6
jap7675 is on a distinguished road
Talking

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. :-)
Reply With Quote
  #7  
Old 12-01-2015, 04:50 PM
Graham Griggs Graham Griggs is offline Applying a formula from a checkbox in VBA Windows 8 Applying a formula from a checkbox in VBA Office 2013
Novice
 
Join Date: Dec 2015
Posts: 11
Graham Griggs is on a distinguished road
Default 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
Reply With Quote
  #8  
Old 12-02-2015, 02:21 AM
Debaser's Avatar
Debaser Debaser is offline Applying a formula from a checkbox in VBA Windows 7 64bit Applying a formula from a checkbox in VBA Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

Quote:
Originally Posted by Graham Griggs View Post
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
It's not unreliable and nor is it any slower than it would be entering the formula yourself in Excel (in fact, it's quicker since you only have to type the formula once).

I also don't follow this comment:
Quote:
If you want to use it repeatedly then write it in Excel
since that's exactly what the code is doing - putting the formula into cells.
Reply With Quote
Reply

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 a formula from a checkbox in VBA 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

Other Forums: Access Forums

All times are GMT -7. The time now is 11:02 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft