Help creating formula in VBA

[Asher]

Hello,

I am trying to figure out how to put these formulas in the VBA code instead of directly into the sheet. Please assist.

My range of cells is Sheet 1, D49 through O49.

In each cell I have a formula that only varies slightly based on the new column.

The structure is this:
Cell D49 Formula: =IFERROR(IF('Sheet 2'!$N$1>=D$47,'Sheet 2'!AF303,""),"")
Cell E49 Formula: =IFERROR(IF('Sheet 2'!$N$1>=E$47,'Sheet 2'!AG303,""),"")
Cell F49 Formula: =IFERROR(IF('Sheet 2'!$N$1>=F$47,'Sheet 2'!AH303,""),"")

and so on through Cell O49 Formula: =IFERROR(IF('Sheet 2'!$N$1>=O$47,'Sheet 2'!AQ303,""),"")


Thanks for any help you can supply.

Asher

[HansV]

Do you mean that you want to put the result of the formulas into the cells?

[Asher]

I replied in gmail then realized I might have to do it here instead so here is my answer:

Yes. The cells are linked to a graph so the result needs to be in there when it's true, which is what the "IF" tells it. If it's false, having the formula in the cell (even though it shows up blank) is registering as a value of 0 on the graph and so the line plummets. That's why I just need all the calculations to go on w/o the formula being inside the actual cell on the sheet that the graph reads.
--Asher

[HansV]

You could fill the cells with the formulas, then replace them with their values:

Code: Select all

With Range("D49:O49")
  .Formula="=IFERROR(IF('Sheet 2'!$N$1>=D$47,'Sheet 2'!AF303,""""),"""")"
  .Value = .Value
End With

[Sundog]

Oh, Hans, that is so slick. With Range whatever, Formula whatever, Value = Value. So I no longer have to copy a calculated column, then Paste Special > Values. I can just insert my formula into this macro and define the range. Excellent.

I'm going to make a Value macro with an easily replaceable range and formula right away, so I can call it up and edit it to the required values whenever I need it.

Do you have a special spreadsheet where you keep all these marvelous code snippets? If not, how do you keep track of them all? Or, heaven forfend, do you make them up out of whole cloth each time? (If so, once the CTP is set up, I'll place my order for a bolt or two [CTP = Cloth Transport Protocol].)

[HansV]

Some are standard macros in my Personal.xlsb, but often I have to write code specifically for a reply.

[Sundog]

Why an .xlsb "Non-XML binary workbook" vs. a Macro-enabled workbook .xlsm?

[HansV]

You should ask Microsoft - the personal macro workbook is a .xlsb in Excel 2007.

[stuck]

Isn't there an option somewhere in XL charts to ignore zeros, so that the chart line doesn't go through the floor?

Ken

[Sundog]

You should ask Microsoft - the personal macro workbook is a .xlsb in Excel 2007.

Huh. OK. Didn't know that. Searched and found it in the XLSTART folder. Is that where yours is?

[HansV]

Yep, it is. That's why it is always loaded when you start Excel.

[Sundog]

Thanks, more good info. I'm adding code as we speak.

[Sundog]

How do I make macros available to all new workbooks? I've tried adding macros to Personal.xlsb, but if I open a new blank workbook and go to Developer > Macros > Edit, any that are in PERSONAL.XLSB are not available for editing.

I'd like all my macros or notes on formulas/code to be in the Excel startup workbook.

[HansV]

Macros in Personal.xlsb can be run from any workbook.

Although you can't edit them by clicking Macros in the Developer tab and clicking Edit, you can edit them from the Visual Basic Editor:
- Click Visual Basic in the Developer tab.
- Expand Personal.xlsb, then Modules, and double-click a module to view and edit the macros stored in that module.

[Sundog]

Would it work if I create a Book workbook and save it in XLSTART, and add my macros to it? That way a "base copy" stays in Book, and when I open a new workbook, I should then be able to edit a macro in it. E.g., if I wanted to run the Values macro in my new workbook opened based on Book, I'd like to be able to edit the macro, and Save As the workbook with a new name, without lousing up the original in Book.

[HansV]

That is a possibility, but it has a serious disadvantage (in my opinion): every blank new workbook that you create will contain the macros, whether you need them there or not. And if you later create an improved version of one of the macros, existing workbooks will still contain and use the older version.

If you store macros in Personal.xlsb, you have one central location for macros, and if you edit a macro, the new version will be available in both new and existing workbooks.

You can protect yourself against messing up macros by backing up Personal.xls frequently; you can also set Excel to make a backup each time you save a new version.

[Sundog]

I understand your reasoning; however my usage might be different than yours.

Point 1:

every blank new workbook that you create will contain the macros, whether you need them there or not.

But I think I want every new workbook to have a base version of the macros. Consider my basic simplified Values macro,

Code: Select all

With Range("B2:B50")
  .Formula = "=A2"
  .Value = .Value
End With
End Sub

A particular workbook might want to use a Range of D2:E78, and a formula of =IF(ISBLANK(A2),"Blank","NotBlank") or something. That version of the Values macro only applies to that particular workbook, and I wouldn't want to change it in Personal, especially if the formula was long and complicated, which would make it harder to select and change. So I'd like to just go to Developer > Macros > select it and edit it right there in the open workbook, without having to switch back and forth to VBA. When I save the open workbook, its customized macro would save along with it.

Point 2:

...if you later create an improved version of one of the macros, existing workbooks will still contain and use the older version.

If I improve a macro, I would improve the one in Book, which would help all new workbooks. My typical use would require a customized macro for each individual workbook, which I would not want to change. True, old workbooks that relied on it would still run the old version, but if it worked for them, why change it?

[HansV]

Agreed, if you need a customized Values macro in every workbook, that makes sense.

[Sundog]

Your help in working through these ideas, as always, has been very valuable. Thanks again.

[Asher]

I don't know if I'm maybe oversimplifying what seems to have become an extremely complex matter, but, why don't you just unhide your personal.xlsb? Once you do that you can edit the macros in it and you don't have to make any new books or anything. Then just hide it again when you are done?