How to add formula to existing macro

[Spellbound]

Hi all

I have a data input worksheet, which uses the following code to fill in the missing zeros when cells are empty.

Please Login or Register  to view this content.

I would like to add a formula to the above code, which conforms to the following based upon Row 2 downwards:

e.g. for F2 it would be

If(AND(D2>0,E2=""),9.5,0)

Tried to utilise some similar examples for this type of code but I they did not work.

TIA ...spellbound

[DonkeyOte]

Spellbound,

Q: you're replacing vbNulls so I'm assuming D:F contain formulae which return Nulls rather than the cells are blank, correct ?

Q: if you're replacing Nulls in E with 0 then your proposed formula will always return 0 as E will never be Null (given you're converting to 0)... so what's your plan ?

[Spellbound]

DonkeyOte

Slightly puzzled by your initial comment as cells in D:F are just left blank and the current code as shown fills in the zeros as per the formatting for each column.

Q: you're replacing vbNulls so I'm assuming D:F contain formulae which return Nulls rather than the cells are blank, correct ?

My knowledge of VBA is extremely limited and this code was originally set up in conjunction with this forum.

With regards to:

Q: if you're replacing Nulls in E with 0 then your proposed formula will always return 0 as E will never be Null (given you're converting to 0)... so what's your plan ?

I just worked that there were multiple instances where Column D was >0 and provided Column E was ="", then the result in Column F would be 9.5 (TRUE) or 0 (FALSE). I used "" rather than zero because I was not sure whether the formula would run before or after the existing code had filled in the zeros.

Perhaps the formula should run first before the Fill_Blanks code, which would become the FALSE result but beyond my knowledge of VBA.

Hope this clarifies things a bit better ...spellbound

[DonkeyOte]

I was enquiring re: formulae & nulls because if the cells are true blanks (ie no formulae in them) then you can identify them with greater ease...

So assuming you're looking to replace blanks in D & E with 0 and insert formulae into F - all based on range as determined by contents of C then perhaps the below will work for you (replaces your code in full):

Please Login or Register  to view this content.

Be sure to run on a test file first... ie ensure you have a roll back position available prior to running the above.

[Spellbound]

Hi DonkeyOte

Thanks for the explanation about true blanks.

Unfortunately, the code does not function in the way that I need it to, as it inserts the formula into every cell in Column F and in doing so overwrites any manual data already inputted into cells within Column F.

e.g. D5=10.00 E5=1 F5=5.00 then the macro formula changes F5=0

hence why I only need the macro formula to only work on rows where D is greater than zero and E is blank, generating 9.5 in Column F.

Your code also generated an error 400 window when I tried to run it again after changing some of the cells in column F to re-test the macro.

Hope you can sort this out …spellbound

[DonkeyOte]

Please Login or Register  to view this content.

Update: F altered to only insert into blank cells - I had assumed the formulae for all rows would be constant... On Error's inserted to handle the fact that once run it's quite possible no blanks will exist when run again.

[Spellbound]

DonkeyOte

Thanks for latest code, as you will see from the attached test workbook, it works fine.

However, discovered that there are odd times when it is possible to have D>”” and E=”” and a manual entry in Column F other than 9.5 although these entries were not altered in my tests by the code you supplied.

My question is whether I need to change the formula in the code to the following or am I quite safe to leave as is?

Please Login or Register  to view this content.

I have also taken the macro a stage further, as the next step after running this code would be to refresh the pivot tables and I combined the code as follows:

Please Login or Register  to view this content.

Again this seems to work but worried about it producing errors due to my very limited knowledge of VBA, especially as the pivot table code was used in a module, whereas it now becomes a worksheet object. So I’m not sure if this changes anything, as I still run the macro from the macro list in the main menu.

Still learning ….spellbound

[DonkeyOte]

I'm not quite sure I follow.. before you said the formulae added to F was only to be added to blank cells... so if F is not blank it will not be overridden regardless of the values in D & E.

This:

Please Login or Register  to view this content.

Would generate a circular reference... the formula say in F2 would be:

Please Login or Register  to view this content.

As you can see this is circular by nature.

So are you saying that if D2 > 0 & E2 = 0 F must be 9.5 post routine regardless of whether or not F was blank before the routine ran ?

It may be easier if you can explain how F is calculated - there is seemingly some relationship between the result in F & the values in D & E ... if you can explain how F is calculated I'm sure we can come up with a quick resolution.

On an aside - re your PT refresh code -- just call the PT routine, ie post End With statement and pre-End Sub insert:

Please Login or Register  to view this content.

[Spellbound]

DonkeyOte

Apologies for the misleading formula and obviously this will produce a circular reference. I was thinking outside of the box, so to speak. All values are manually entered into D,E and F, there is no applicable formula. I was simply looking at a way of using VBA to fill the empty cells, reducing manual data entry.

Your statement:

so if F is not blank it will not be overridden regardless of the values in D & E.

answers my question, as you have confirmed that if “F” is NOT blank it will NOT be overridden.

Just for the record, I have attached a workbook showing the data filled in before the macro would be run.

Thanks for the tip on how to call the PT routine.

In due course, I hope to try and replace all of this with a specific data input form for this data.

Regards …spellbound

[DonkeyOte]

Spellbound, so to confirm, is this resolved ?

[Spellbound]

Marked as "solved", thanks again DonkeyOte...