Formulas in VBA/Macro

[Shaner73]

I'm trying put these Formulas in VBA so I don't have to worry about someone deleting the formula
if the have to delete a row or anything else that can cause the formula to break. I've tried to
Lock and Hide the formuals, but that doesn't work the way it needs to. This worksheet will be on
a shared drive for multiple to be able to access and work in.

The worksheet is these formulas are in is called InvTracker (this is in Sheet1). This is saved as .xlsm.

The Range goes from Row 2 to Row 5000 of the Columns I have listed next to the Formula.

I'm not sure if I need to insert a new module, or if I can add it to the existing Private Sub where
I have a Pivot Table to auto refresh.

**Code for reference:**
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot for Avg Use").PivotTables("PivotTable3").PivotCache.Refresh
End Sub

Can anyone help me out? I've searched and tried several possibilities, but nothing has worked.

Column C =IF(B2="","",B2-DATE(YEAR(B2),1,1)+1)
Column G =IF(F2="","",VLOOKUP([@[Product Name]],'Vend_Prod (2)'!C:D,2,FALSE))
Column I =IF(G2="","",VLOOKUP(G2,'Vend_Prod (2)'!D:E,2,FALSE))
Column J =IF(H2="","",VLOOKUP([@[Product Name]],MaxStockQty!A:E,5,FALSE))
Column K =IF(F2="","",VLOOKUP([@[Product Name]],'Vend_Prod (2)'!C:H,6,FALSE))
Column L =IF(F2="","",VLOOKUP([@[Product Name]],'Vend_Prod (2)'!C:G,5,FALSE))
Column M =IF(H2="","",VLOOKUP([@[Product Name]],'Pivot for Avg Use'!A:D,3,FALSE))
Column N =IF(M2="","",J2/M2)
Column O =IF(N2="","",IF(N2>=L2+14,"No","Yes"))

[Richard Buttrey]

You can include the instruction anywhere.
The important thing to remember when getting VBA to add formula is that any literal quotes that Excel needs have to be included in a pair of quotes in VBA. So your first instruction would be

Please Login or Register  to view this content.

[Shaner73]

I've tried a couple like this, but they did not work. I've tried adding into the existing macro, as well as insert a new module. Still didn't work. I must be doing something wrong.

[Richard Buttrey]

I've tried a couple like this, but they did not work. I've tried adding into the existing macro, as well as insert a new module. Still didn't work. I must be doing something wrong.

Indeed you must.

Upload the workbook and no doubt we can help you out. Tell us where you expect the formula to go and what event should trigger the macro.

[Shaner73]

Here is a Test copy...

I "assume" the formulas will in the Macro for the InvTacker sheet, but not real sure. For right now, all work will be done in the InvTracker sheet.

As for the trigger...as soon as you tab from the cell to the left...so I guess that's "Active Cell" in that column?

[Richard Buttrey]

Hi,

Here's the Sheet Change macro that will give effect to your first formula.

Please Login or Register  to view this content.

Hopefully you can extend the logic to your other cells and add other

Please Login or Register  to view this content.

code blocks.

[Shaner73]

Thank you...however, I'm not sure what you mean by extending the logic...and then the code. I'm sure it has to do with adding in the other formulas, but I honestly have no clue how to do that...sorry.

I am very limited to Excel VBA and macro's.

[Shaner73]

Anyone else have suggestions of advice??? I cannot get the above given code coded to work with multiple formulas.

I've even tried recording the macro.

[Richard Buttrey]

Hi,

See attached,

I've added these two code blocks in the Sheet1 Change event procedure. You need to add similar blocks for your other 7 formula that you mention

Please Login or Register  to view this content.

[Shaner73]

Thanks Richard...however, it's not working. It seems to be something with the VLOOKUP formulas.

I have attached 2 pics - one that shows the formula is not correct in the cell, and the other that there is no formula in the cell.

Here is the code I'm using...

Please Login or Register  to view this content.

[Richard Buttrey]

Hi,

I forgot to mention that I needed to change the range name 'Vend_Prod' that was scoped to the Vend_Prod (2) sheet A1:D16 t C1:D16

i.e. the Range name is now ='Vend_Prod (2)'!$C$1:$D$16

See attached workbook.

Re the 3rd formula you've added. You probably need to similarly change the range name 'Vend_Prod' that is scoped to the MaxStockQty sheet to B1:C16 on that sheet.

For future proofing your workbook you should really create dynamic range names so that they adjust automatically to differing numbers of values in named ranges

[Shaner73]

I'm really not familiar with this type of VBA. The VBA I worked with before was very basic...more-less a lot of just simple recording macro's and Selects.

I'll do my best with this...if I can't get it to work from here, I'll just leave the formula's in the cell and take my chances.

Thanks Richard! I appreciate it.

[Shaner73]

This is solved...I figured something else out that works and was fairly simple. Thanks again, Richard, for your help.