Some formulas return a #REF! error which is correct because the account does not exist that it is looking for. What is a good code to use to say if REF error is returned, replace the value in the cell with 0?
Some formulas return a #REF! error which is correct because the account does not exist that it is looking for. What is a good code to use to say if REF error is returned, replace the value in the cell with 0?
Old Way:
If(IsError(Original Formula),0,Original Formula,?,?))))))))))... however many ))))))))) as needed
Replace "Original Formula" with the original Formula. ",?,?" represents the return from the original Formula.
Last edited by SamT; 08-12-2017 at 07:55 AM.
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
That works great, thank you.
WE have IFERROR now
=IFERROR(formula,"")
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I wrote this useful macro to put iferror around an existing formula, I use it all the time:
Sub addiferror() cform = ActiveCell.Formula cform2 = Mid(cform, 2, 2555) cform3 = "=iferror(" & cform2 & ",0)" ActiveCell.Formula = cform3 End Sub
Hi Offthelip
I would be concerned if errors were returned of which I was not aware. I'd suggest adding this to allow your 0 entry but highlight the issue and allow dependent cells to function
ActiveCell.NumberFormat = "0;0;""Err"""
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Thanks md that is a very nice addition.
Originally Posted by offthelip
Why the 2555? Did you mean 255?
Or maybe
cform2 = Right(2, Len(cform) -1)
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
The 2555 was just big enough number to accomodate any length of formula that I was ever likely to write. I probably started off thinking of 255 because that is an obvious number, but realised that that might not be sufficient so put in another "5". I don't actually know what the length limit is for a formula in Excel. I have always found it sufficient, even when I use vba to write the formualae.
https://support.office.com/en-gb/art...7-269d656771c3
Length of formula contents
8,192 characters
Internal length of formula
16,384 bytes
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
@ kmlartigue
Apologies for hi-jacking your thread!
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
NP - it was just an odd number
---------------------------------------------------------------------------------------------------------------------
Paul
Remember: Tell us WHAT you want to do, not HOW you think you want to do it
1. Use [CODE] ....[/CODE ] Tags for readability
[CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
2. Upload an example
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
3. Mark the thread as [Solved] when you have an answer
Thread Tools (on the top right corner, above the first message)
4. Read the Forum FAQ, especially the part about cross-posting in other forums
http://www.vbaexpress.com/forum/faq...._new_faq_item3
Just FYI, you don't actually need a number there at all. Leave it out to just return all the rest of the text:
cform2 = Mid(cform, 2)
Be as you wish to seem
ActiveCell.Formula = "=iferror(" & Mid(ActiveCell.Formula, 2) & ",0)"
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
That is what I like, reduce my 4 lines of code to 1 line. Can I submit the rest of my file of UDFs and useful macros for review please??
That should be fun! (How many years of VBA?) I'd be embarrassed to post some of mine.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
I'd be embarrassed to post any of mine with Rory lurking!
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Ha! The other way around more like!
Be as you wish to seem
Thank you, I will try those suggestions out.
I ended up using this way and it works great.
Old Way:
If(IsError(Original Formula),0,Original Formula,?,?))))))))))... however many ))))))))) as needed
Posting Permissions
Reply With Quote
