I have the following formula in one column (W) in a big data base. It works fine but when rows
are inserted for subtotals or whatever all the formulas return a #VALUE error wrong data type!
If you select the cell and hit F2 edit and return it corrects itself. I replaced IsFormula with IsNumber
for a test with same results.
=IF(IsFormula(V4),V4*E4,V4*Crew1_Labor_Pct)
Any thoughts?
ThanksTom
I think we need to see what the data is like to really be able to debug it.
Can you post the spreadsheet?
What does Crew1_Labor_Pct pertain to? Is it a named range? i.e. one cell with a value in?
As hectop states, probably best if we could see the data. Anonymise it if it contains anything sensitive.
I have 2007, which does not have a =IsFormula() function, so I am unsure if 2010 has that function.
However, if that is a direct copy from the cell you have it in...
=IF(IsFormula(V4),V4*E4,V4*Crew1_Labor_Pct)
then it looks like that is not a function in 2010 either, else the "IsFormula" would be all in caps "ISFORMULA"
Also, check to see what the contents of V4 is, an error there will give an error in the formulas
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
No =ISFORMULA() in my copy of Excel 2010...Originally Posted by FDibbins
>>I have 2007, which does not have a =IsFormula() function, so I am unsure if 2010 has that function.
No it doesn't so I made my own function (actually found on web) as follows
Function IsFormula(MyRange As Range) As Boolean
IsFormula = False
If MyRange.HasFormula Then IsFormula = True
'Application.Volatile True I commented this out trying to debug, not sure what it does
End Function
I went back to my spreadsheet and replaced IsFormula() with ISNUMBER() and the problem disappeared!!
I believe my IsFormula is at fault?
>>I have 2007, which does not have a =IsFormula() function, so I am unsure if 2010 has that function.
No it doesn't so I made my own function (actually found on web) as follows
Function IsFormula(MyRange As Range) As Boolean
IsFormula = False
If MyRange.HasFormula Then IsFormula = True
'Application.Volatile True I commented this out trying to debug, not sure what it does
End Function
I went back to my spreadsheet and replaced IsFormula() with ISNUMBER() and the problem disappeared!!
I believe my IsFormula is at fault?
TRIED AGAIN with the following vba function
Function CELLHASFORMULA(cell) As Boolean
CELLHASFORMULA = cell.HasFormula
End Function
Same #VALUE error when rows inserted
Using EXCEL'S ISNUMBER function in the formula creates no #VALUE error
Will change logic to use Excel's function. User functions prone to internal addressing errors???
THANKS to everyone who responded. I am new and will try to improve my posting skills
TOM
i cannot reproduce that error if i use the same code.i can insert rows columns above/below/in between just fine (2007)
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Martin
OK trying to attach spread sheet to show error (newbie sorry)
Columns W and AD have the formula with the CELLHASFORMULA user function
Function CELLHASFORMULA(cell) As Boolean
CELLHASFORMULA = cell.HasFormula
End Function
Thanks
Last edited by Thomas J; 11-03-2013 at 04:29 PM.
once i get rid of value error i can insert where are you trying to insert a row?
Insertion points are random as chosen by user. A ribbon function called "Subtotal row" under custom ribbon "lowbid"
was used to insert rows. it is under development and only is coded for row insertion and border underling.
this is the code I used
Thanks
Please Login or Register to view this content.
Last edited by FDibbins; 11-02-2013 at 09:23 PM. Reason: code tags added
Thomas, plese use code tags when posting VBA code...
Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.
Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here
First of all apologies to everyone and thanks for putting up with
a first timer. To explain my problem properly I have attach a file
with a formula in column W that returns a #VALUE when I run Macro named "InsertSubtotal".
The formula in column W relies on CELLHASFORMULA funtion to determine if there is a formula in the cell.
Both of these are in Module1 in the VBA editor.
I am trying to write code to let users insert subtotals in the columns which already have the grand totals
So pick a row, insert two rows, border top row for an underline is all it does now.
But when it runs I get the #VALUE error. Insert rows manually (right click row & insert)works fine??
Also edit cell (F2) and formula corrects!!
Thanks Again.
I know you want VBA for this, but just an FYI...If you use =subtotal() for your subtotals, you can then also use =subtotal() for the grand total - includiing the entire range (as well as all the ST's above), and it will give you the grand total excluding the subtotals aboveI am trying to write code to let users insert subtotals in the columns which already have the grand totals
Yes thanks for the advice. I believe the correct entry would be =subtotal(9,cell1:cell2)?
Yes correct, subtotal(9,range)
OR, if you might be filtering out a few rows and want them excluded from the total, use
subtotal(109,range)
Problem Solved. Turned off auto calculate then turned it back on after macro inserted rows.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks