PDA

View Full Version : Add a Argument to Specific Formulas VBA



Shazam
08-26-2015, 01:06 PM
Hi,

A couple of users here are using Biznet. It's a reporting & analysis software. It's basically an add-in to your Excel workbook.

http://www.swktech.com/uploads/2014/01/bizinsight_product_brochure_web.pdf

(http://www.swktech.com/uploads/2014/01/bizinsight_product_brochure_web.pdf)Biznet have updated their software and now requires to to use additional argument in their functions.

My problem is that the users has allot of workbooks that also contains allot of worksheet tabs using the Biznet formulas.

Is their a VBA code I can run that can sip through all Biznet formulas on that particular worksheets and add the additional argument (Consolidated) to the Biznet formulas?

Look at the attachment below.

All I needed is the "Consolidated" to be used in all BizNet formula Functions. If you look at attachment Capture2.PNG, the Consolidated argument always has to be reference.

Shazam
08-31-2015, 08:28 AM
If it's not clear what I'm saying or not possible, please let me know.

mancubus
08-31-2015, 02:12 PM
i believe most of our members don't have biznet add-in installed.

therefore you'll need to explain what a biznet formula is and how can we can differentiate it from built-in excel formulas.

then try to visualize what modifications are needed by providing 'before macro' biznet formulas and 'after macro' biznet formulas.


to give you an idea below macro inserts =IfError before and ,0 after the original formula to all formula cells.
Right(cll.Formula, Len(cll.Formula) - 1) is used for removing the = sign in original formulas.



Sub AddIfErrorToExistingFormulas()

Dim cll As Range

For Each cll In Worksheets("Sheet1").Cells.SpecialCells(xlCellTypeFormulas)
If Not cll.HasArray And Left(cll.Formula, 8) <> "=IfError" Then
cll.Formula = "=IfError(" & Right(cll.Formula, Len(cll.Formula) - 1) & ",0)"
End If
Next

End Sub