I am getting the #VALUE! display when a variable is missing in formula. In the formula =IF((J2+L2)=0," ",(K2*1000)/(J2+L2)), L2 is zero in many cases but K2 and J2 have values which are non-zero.
Any suggestions?
I am getting the #VALUE! display when a variable is missing in formula. In the formula =IF((J2+L2)=0," ",(K2*1000)/(J2+L2)), L2 is zero in many cases but K2 and J2 have values which are non-zero.
Any suggestions?
Last edited by Jim15; 01-25-2016 at 01:18 PM.
Jim15
Hi Jim,
It can be difficult without the data to understand the need. Try these two formula and see if they work for you.
Formula:=IF((J2+L2)=0,"",K2*1000/(J2+L2))
or
Formula:=IF(OR(J2=0,L2=0),"",(K2*1000)/(J2+L2))
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Attached is the Test Macro Buttons Excel spreadsheet and there are three (3) buttons currently used. Abstract, Annual and Monthly on the far left under Macros. If a field is blank, it gives the #VALUE! instead of the actual answer. According to some of the equations, I was expecting the fields to be calculated if a variable was missing and I understand the #DIV/0! I would prefer the #VALUE! to be calculated and the #DIV0! to be blank.
Thanks!
I don't see any buttons or code.
Click the * Add Reputation button in the lower left hand corner of this post to say thanks.
Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
This is an add-in so how do I send that with an Excel file?
If you are in the visual basic editor you should be able to choose the add in project and see the code.
You can upload the code.
Sub Prod_Abstract() ' ' Prod_Abstract Macro ' Columns("L:L").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("L1").Select ActiveCell.FormulaR1C1 = "BOE" Range("L2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]+RC[-2]/6)" Range("L2").Select Selection.AutoFill Destination:=Range("L2:L187") Range("L2:L187").Select Columns("M:M").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("M1").Select ActiveCell.FormulaR1C1 = "MMCFE" Range("M2").Select ActiveCell.FormulaR1C1 = "=SUM((RC[-4]*6+RC[-3])/1000)" Range("M2").Select Selection.AutoFill Destination:=Range("M2:M187") Range("M2:M187").Select Columns("N:N").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("N1").Select ActiveCell.FormulaR1C1 = "BCFE" Range("N2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/1000)" Range("N2").Select Selection.AutoFill Destination:=Range("N2:N187") Range("N2:N187").Select Columns("N:N").Select Selection.NumberFormat = "#,##0.000" Selection.NumberFormat = "#,##0.0" Columns("O:O").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("O1").Select ActiveCell.FormulaR1C1 = "Yield, BO/MMCF" Range("O2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-6]/RC[-5]/1000)" Range("O2").Select Selection.AutoFill Destination:=Range("O2:O187") Range("O2:O187").Select Range("O2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-6]/(RC[-5]/1000))" Range("O2").Select Selection.AutoFill Destination:=Range("O2:O187") Range("O2:O187").Select Columns("P:P").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("P1").Select Columns("O:O").ColumnWidth = 15.14 Range("P1").Select ActiveCell.FormulaR1C1 = "GOR" Range("P1").Select ActiveCell.FormulaR1C1 = "GOR, cf/BO" Range("P2").Select ActiveCell.FormulaR1C1 = "=SUM((RC[-6]*1000)/RC[-7])" Range("P2").Select Selection.AutoFill Destination:=Range("P2:P187") Range("P2:P187").Select Columns("P:P").Select Selection.NumberFormat = "#,##0" Range("Q6").Select ActiveWindow.ScrollColumn = 2 ActiveWindow.ScrollColumn = 3 ActiveWindow.ScrollColumn = 4 ActiveWindow.ScrollColumn = 5 ActiveWindow.ScrollColumn = 6 ActiveWindow.ScrollColumn = 7 ActiveWindow.ScrollColumn = 8 ActiveWindow.SmallScroll Down:=0 Columns("R:R").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("R1").Select ActiveCell.FormulaR1C1 = "BOPD" Range("R2").Select Application.WindowState = xlMaximized ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/30)" Range("R2").Select Selection.AutoFill Destination:=Range("R2:R187") Range("R2:R187").Select Columns("R:R").Select Selection.NumberFormat = "#,##0" Columns("T:T").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("T1").Select ActiveCell.FormulaR1C1 = "BOPD" Range("T2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/30)" Range("T2").Select Selection.AutoFill Destination:=Range("T2:T187") Range("T2:T187").Select Columns("T:T").Select Selection.NumberFormat = "#,##0" Columns("V:V").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("W:W").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("V1").Select Selection.NumberFormat = "#,##0" ActiveCell.FormulaR1C1 = "BOPM" Range("V2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/12)" Range("V3").Select ActiveWindow.SmallScroll Down:=-3 Range("V2").Select Selection.AutoFill Destination:=Range("V2:V187") Range("V2:V187").Select Range("W1").Select ActiveCell.FormulaR1C1 = "BOPD" Range("W2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/365)" Range("W2").Select Selection.AutoFill Destination:=Range("W2:W187") Range("W2:W187").Select Columns("V:W").Select Selection.NumberFormat = "#,##0" Range("W12").Select ActiveWindow.ScrollColumn = 9 ActiveWindow.ScrollColumn = 10 ActiveWindow.ScrollColumn = 11 ActiveWindow.ScrollColumn = 12 ActiveWindow.ScrollColumn = 13 ActiveWindow.ScrollColumn = 14 ActiveWindow.ScrollColumn = 15 ActiveWindow.ScrollColumn = 16 ActiveWindow.ScrollColumn = 17 ActiveWindow.ScrollColumn = 18 ActiveWindow.ScrollColumn = 19 ActiveWindow.ScrollColumn = 20 Columns("Y:Z").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("Y1").Select Selection.NumberFormat = "#,##0" ActiveCell.FormulaR1C1 = "BOPD" Range("Y2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/12)" Range("Y2").Select Selection.AutoFill Destination:=Range("Y2:Y187") Range("Y2:Y187").Select Range("Z1").Select Selection.NumberFormat = "#,##0" ActiveCell.FormulaR1C1 = "BOPD" Range("Z2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/365)" Range("Z2").Select Selection.AutoFill Destination:=Range("Z2:Z187") Range("Z2:Z187").Select Columns("Y:Z").Select Selection.NumberFormat = "#,##0" Columns("AB:AC").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("AB1").Select Selection.NumberFormat = "#,##0" ActiveCell.FormulaR1C1 = "BOPD" Range("AB2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/12)" Range("AB1").Select ActiveCell.FormulaR1C1 = "BOPM" Range("Y1").Select ActiveCell.FormulaR1C1 = "BOPM" Range("AB2").Select Selection.AutoFill Destination:=Range("AB2:AB187") Range("AB2:AB187").Select Range("AC1").Select Selection.NumberFormat = "#,##0" ActiveCell.FormulaR1C1 = "BOPD" Range("AC2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/365)" Range("AC2").Select Selection.AutoFill Destination:=Range("AC2:AC187") Range("AC2:AC187").Select Columns("AB:AC").Select Selection.NumberFormat = "#,##0" Columns("AE:AE").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("AE1").Select Selection.NumberFormat = "#,##0" ActiveCell.FormulaR1C1 = "MCFD" Range("AE2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/30)" Range("AE2").Select Selection.AutoFill Destination:=Range("AE2:AE187") Range("AE2:AE187").Select ActiveWindow.ScrollColumn = 21 ActiveWindow.ScrollColumn = 22 ActiveWindow.ScrollColumn = 23 ActiveWindow.ScrollColumn = 24 ActiveWindow.ScrollColumn = 25 ActiveWindow.ScrollColumn = 26 Columns("AG:AG").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("AG1").Select Selection.NumberFormat = "#,##0" ActiveCell.FormulaR1C1 = "MCFD" Range("AG2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/30)" Range("AG2").Select Selection.AutoFill Destination:=Range("AG2:AG187") Range("AG2:AG187").Select Columns("AI:AI").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Columns("AJ:AJ").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("AI1").Select Selection.NumberFormat = "#,##0" ActiveCell.FormulaR1C1 = "MCFM" Range("AI2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/12)" Range("AI2").Select Selection.AutoFill Destination:=Range("AI2:AI187") Range("AI2:AI187").Select Range("AJ1").Select ActiveCell.FormulaR1C1 = "MCFD" Range("AJ2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/365)" Range("AJ2").Select Selection.AutoFill Destination:=Range("AJ2:AJ187") Range("AJ2:AJ187").Select ActiveWindow.ScrollColumn = 27 ActiveWindow.ScrollColumn = 28 ActiveWindow.ScrollColumn = 29 ActiveWindow.ScrollColumn = 30 Columns("AL:AL").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("AL1").Select ActiveCell.FormulaR1C1 = "MCFM" Range("AL2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/12)" Range("AL2").Select Selection.AutoFill Destination:=Range("AL2:AL187") Range("AL2:AL187").Select Columns("AM:AM").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("AM1").Select ActiveCell.FormulaR1C1 = "MCFD" Range("AM2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/365)" Range("AM2").Select Selection.AutoFill Destination:=Range("AM2:AM187") Range("AM2:AM187").Select Columns("AO:AO").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("AO1").Select ActiveCell.FormulaR1C1 = "MCFM" Range("AO2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-1]/12)" Range("AO2").Select Selection.AutoFill Destination:=Range("AO2:AO187") Range("AO2:AO187").Select Columns("AP:AP").Select Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Range("AP1").Select ActiveCell.FormulaR1C1 = "MCFM" Range("AP2").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-2]/365)" Range("AP2").Select Selection.AutoFill Destination:=Range("AP2:AP187") Range("AP2:AP187").Select Columns("AO:AP").Select Selection.NumberFormat = "#,##0" Range("AQ12").Select ActiveWindow.ScrollColumn = 31 ActiveWindow.ScrollColumn = 32 ActiveWindow.ScrollColumn = 33 ActiveWindow.ScrollColumn = 34 End Sub Sub Prod_Annual() ' Dim i As Integer ' ' Macro recorded 8/12/2005 by Jim Williams ' ' Keyboard Shortcut: Ctrl+r ' ' MsgBox ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row ' Range("P1").Select ActiveCell.FormulaR1C1 = "GOR" Range("Q1").Select ActiveCell.FormulaR1C1 = "GLR" Range("R1").Select ActiveCell.FormulaR1C1 = "WOR" Range("S1").Select ActiveCell.FormulaR1C1 = "Water Cut" Range("T1").Select ActiveCell.FormulaR1C1 = "Gas to Oil, mcf/bbl" Range("U1").Select ActiveCell.FormulaR1C1 = "Oil to Gas, bbl/mmcf" Range("V1").Select ActiveCell.FormulaR1C1 = "Wtr to Gas, bbl/mmcf" Range("W1").Select ActiveCell.FormulaR1C1 = "Daily Oil, bbl" Range("X1").Select ActiveCell.FormulaR1C1 = "Daily Gas, mcf" Range("Y1").Select ActiveCell.FormulaR1C1 = "Daily Wtr, bbl" Range("Z1").Select ActiveCell.FormulaR1C1 = "Oil Decline" Range("AA1").Select ActiveCell.FormulaR1C1 = "Gas Decline" Range("AB1").Select ActiveCell.FormulaR1C1 = "Water Decline" ' Range("P2").Select ' For i = 1 To Selection.CurrentRegion.Rows.Count - 1 ' ' Calculate columns P - Y. ' Cells(ActiveCell.Row, "P").FormulaR1C1 = "=IF(RC[-6]=0,"" "",(RC[-5]*1000)/RC[-6])" Cells(ActiveCell.Row, "Q").FormulaR1C1 = "=IF((RC[-7]+RC[-5])=0,"" "",(RC[-6]*1000)/(RC[-7]+RC[-5]))" Cells(ActiveCell.Row, "R").FormulaR1C1 = "=IF(RC[-8]=0,"" "",RC[-6]/RC[-8])" Cells(ActiveCell.Row, "S").FormulaR1C1 = "=IF((RC[-9]+RC[-7])=0,"" "",RC[-7]/(RC[-9]+RC[-7]))" Cells(ActiveCell.Row, "T").FormulaR1C1 = "=IF(RC[-10]=0,"" "",RC[-9]/RC[-10])" Cells(ActiveCell.Row, "U").FormulaR1C1 = "=IF(RC[-10]=0,"" "",(RC[-11]*1000)/RC[-10])" Cells(ActiveCell.Row, "V").FormulaR1C1 = "=IF(RC[-11]=0,"" "",(RC[-10]*1000)/RC[-11])" Cells(ActiveCell.Row, "W").FormulaR1C1 = "=SUM(RC[-13]/365)" Cells(ActiveCell.Row, "X").FormulaR1C1 = "=SUM(RC[-13]/365)" Cells(ActiveCell.Row, "Y").FormulaR1C1 = "=SUM(RC[-13]/365)" Cells(ActiveCell.Row, "Z").FormulaR1C1 = "=IF(ISERROR(SUM(RC[-16]/R[-1]C[-16] -1)),"" "",(RC[-16]/R[-1]C[-16] -1))" Cells(ActiveCell.Row, "AA").FormulaR1C1 = "=IF(ISERROR(SUM(RC[-16]/R[-1]C[-16] -1)),"" "",(RC[-16]/R[-1]C[-16] -1))" Cells(ActiveCell.Row, "AB").FormulaR1C1 = "=IF(ISERROR(SUM(RC[-16]/R[-1]C[-16] -1)),"" "",(RC[-16]/R[-1]C[-16] -1))" ' ' Next row. ' ActiveCell.Offset(1, 0).Select ' Next i ' ' Format columns P - Y. ' Columns("P:R").Select Selection.NumberFormat = "#,##0" Columns("S:S").Select Selection.NumberFormat = "0%" Columns("T:V").Select Selection.NumberFormat = "#,##0.0" Columns("Y:Z").Select Selection.NumberFormat = "#,##0.0" Columns("Z:AB").Select Selection.NumberFormat = "0%" Cells.Select Range("H1").Activate Selection.Columns.AutoFit End Sub
Last edited by Jim15; 01-21-2016 at 05:03 PM.
Jim you have quite a few posts, so I'm going to have to insist you use code tags. Go to your last post, choose the edit button, select all the code text and click the # button on the toolbar. This will put code tags at the beginning and end of your code. Choose to save the changes and your code will look like mine below.
Please make this a habit when posting code.
Sub Test() End Sub
Finished adding code tags and thanks for the tip. There are two (2) macros in the code Prod_Abstract and Prod_Annual. Prod_Annual is cleaner and more succinct whereas Prod_Abstract is a recorded macro.
You have an empty string in your cells which is causing the errors in your formulas.
In other words the cells are not blank, it's the equivalent of clicking into a cell and pushing the space bar and hitting enter.
When I click into a cell that looks empty and hit the delete key, removing the empty string, the formula works fine.
Thanks. Is there VBA code that I can add that will remove the empty strings?
btw all your sum() functions are unnecessary
Josie
if at first you don't succeed try doing it the way your wife told you to
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks