Need Help to change formulas to VBA

[NeoSez]

So long story short, this person keeps erasing all the formulas every single time they use the spreadsheet.
How do I take all the column formulas and convert them all into a VBA?
For example, I found this code from Trevor(?)
This was just for R2. What if I have multiple cells with different formulas? How to put them all together easily? I am not very good with VBA as I am still very new. Do I keep adding a new line and add the formula after?

Range("R2").Formula = '(insert formula here)

Running that macro record button was just making a bigger mess that I am unable to comprehend as the final result was incredibly long and made no sense to me.
Help please. Thank you.

Option Explicit
Sub Macro2()

    Dim lngLastRow As Long
    
    lngLastRow = Cells(Rows.Count, "K").End(xlUp).Row
    
    Range("R2").Formula = "=SUM(OFFSET($Q$7,COUNT(K7:K" & lngLastRow & ")-P1,0,P1))/SUM(OFFSET($J$7,COUNT(K7:K" & lngLastRow & ")-P1,0,P1))"

End Sub

 

[Zot]

It would help if you list few sample of formula you want to write. If each cell is different formula, then you have to write each one in code.

Do you want to write formula in same column, multiple column or random location?

 

[NeoSez]

It would help if you list few sample of formula you want to write. If each cell is different formula, then you have to write each one in code.

Do you want to write formula in same column, multiple column or random location?

Hi Zot. The formulas are pretty much everywhere. Almost every column has a formula, then there are formulas in a singled out cell to be part of a bigger formula. Eg. a formula in A1 by itself, is part of a formula like

=PROPER(INDIRECT("F"&MIN(IF(SUBTOTAL(3,OFFSET(F5,ROW(F5:F100)-ROW(F5),,1)),ROW(F5:F100)))))

Another formula concatenates a whole string of things.

Another one-off tallies

=COUNTIFS(F:F,$E$1,G:G,">"&$H$1-1)

OR a simple cell formula that part of a larger formula:

=TODAY()

I want to compile all the formulas in the sheet, into a VBA script so formulas can't be deleted every time it's accessed. There are just too many formulas to list. I just require some guidance on how to do so, as I am quite new to VBA.

 

[maabadi]

Please Check This address for Locking all Cell with Formula to Cannot Change without password.
https://www.ablebits.com/office-addins-blog/2016/01/14/hide-lock-formulas-excel/

OR

https://www.extendoffice.com/documents/excel/935-excel-lock-protect-formulas.html

 

[Zot]

Hi Zot. The formulas are pretty much everywhere. Almost every column has a formula, then there are formulas in a singled out cell to be part of a bigger formula. Eg. a formula in A1 by itself, is part of a formula like

=PROPER(INDIRECT("F"&MIN(IF(SUBTOTAL(3,OFFSET(F5,ROW(F5:F100)-ROW(F5),,1)),ROW(F5:F100)))))

Another formula concatenates a whole string of things.

Another one-off tallies

=COUNTIFS(F:F,$E$1,G:G,">"&$H$1-1)

OR a simple cell formula that part of a larger formula:

=TODAY()

I want to compile all the formulas in the sheet, into a VBA script so formulas can't be deleted every time it's accessed. There are just too many formulas to list. I just require some guidance on how to do so, as I am quite new to VBA.

If you just want to rewrite formula to their designated cell, then you can just list down all formula in their designated range using the syntax you have.

Range("R2").Formula = '(insert formula here)

Just change R2 to the range you wanted. Keep listing all ranges and their formula in the macro. IF the location is fixed location then just copy as is. Example:
1) Range("R2").Formula = "=PROPER(INDIRECT(""F""&MIN(IF(SUBTOTAL(3,OFFSET(F5,ROW(F5:F100)-ROW(F5),,1)),ROW(F5:F100)))))
2) Range ("L6").Formula = "=TODAY()"

Note if there is double quote in expression, you need to put twice. Otherwise it is considered as end of formula quote. It is tricky to me too ?

You can refer here

Put Double Quotes in a String in Excel VBA - ExcelBaby

You will learn: How to put double quotes in a string that already has a quote or double quote in Excel VBA. Take this free course now!

www.excelhowto.com

 

[NeoSez]

If you just want to rewrite formula to their designated cell, then you can just list down all formula in their designated range using the syntax you have.

Range("R2").Formula = '(insert formula here)

Just change R2 to the range you wanted. Keep listing all ranges and their formula in the macro. IF the location is fixed location then just copy as is. Example:
1) Range("R2").Formula = "=PROPER(INDIRECT(""F""&MIN(IF(SUBTOTAL(3,OFFSET(F5,ROW(F5:F100)-ROW(F5),,1)),ROW(F5:F100)))))
2) Range ("L6").Formula = "=TODAY()"

Note if there is double quote in expression, you need to put twice. Otherwise it is considered as end of formula quote. It is tricky to me too ?

You can refer here

Put Double Quotes in a String in Excel VBA - ExcelBaby

You will learn: How to put double quotes in a string that already has a quote or double quote in Excel VBA. Take this free course now!

www.excelhowto.com

Thanks for the tips Zot. I'll give it go and see how far I get. If I get in trouble, I'll come back here and ask the experts.

 

[Akuini]

Hi @NeoSez
Here's my suggestion:
1. Copy the sheet in question (say "sheet1") to a helper sheet.
2. After the user has finished editing data in sheet1, you can get the formula back from the helper sheet. To do this, you can use this macro:

Sub a1157656a()
Dim r As Range

For Each r In Cells.SpecialCells(xlCellTypeFormulas)
    Sheets("Sheet1").Range(r.Address).Formula = r.Formula
Next

End Sub

Note:
1. The helper sheet must be the active sheet when you run the macro.
2. Change Sheets("Sheet1") to suit.

 

[NeoSez]

Hi @NeoSez
Here's my suggestion:
1. Copy the sheet in question (say "sheet1") to a helper sheet.
2. After the user has finished editing data in sheet1, you can get the formula back from the helper sheet. To do this, you can use this macro:

Sub a1157656a()
Dim r As Range

For Each r In Cells.SpecialCells(xlCellTypeFormulas)
    Sheets("Sheet1").Range(r.Address).Formula = r.Formula
Next

End Sub

Note:
1. The helper sheet must be the active sheet when you run the macro.
2. Change Sheets("Sheet1") to suit.

[SIZE=17px]Akuini[/SIZE] That's a great idea as well. Thank you.

 

[Zot]

[SIZE=17px]Akuini[/SIZE] That's a great idea as well. Thank you.

Akuini method is hassle-free and fastest

 

[Akuini]

[SIZE=17px]Akuini[/SIZE] That's a great idea as well. Thank you.

You're welcome, glad to help.