Need Help to change formulas to VBA

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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?
VBA Code:
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
922
Office Version
  1. 2016
Platform
  1. Windows
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

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Excel Formula:
=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
Excel Formula:
=COUNTIFS(F:F,$E$1,G:G,">"&$H$1-1)

OR a simple cell formula that part of a larger formula:
Excel 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.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
922
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Excel Formula:
=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
Excel Formula:
=COUNTIFS(F:F,$E$1,G:G,">"&$H$1-1)

OR a simple cell formula that part of a larger formula:
Excel 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
 

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,955
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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:

VBA Code:
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.
 
Solution

NeoSez

Board Regular
Joined
Aug 14, 2020
Messages
146
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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:

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,996
Messages
5,622,078
Members
415,875
Latest member
Tarali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top