Forum Discussion

Nishkarsh31's avatar
Nishkarsh31
Brass Contributor
Jul 25, 2022
Solved

Can we write a VBA Code, that automatically overwrites cells values of a formula on workbook close?

I have a table formula that calculates total amount based on given prices. The trouble is when the prices change, it overwrites the formula of Dates which have already passed, And the ledger neve...
excel
Excel on mobile
Formulas and Functions
Macros and VBA
office 365
Office Scripts
  • HansVogelaar's avatar
    HansVogelaar
    Jul 25, 2022

    Nishkarsh31 

    In the Visual Basic Editor, double-click ThisWorkbook under Microsoft Excel Objects.

    Copy the following code into the ThisWorkbook module:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rng As Range
    With Worksheets("Data")
        For Each rng In .Range(.Range("A2"), .Range("A1").End(xlDown))
            If rng.Value <= Date Then
                With rng.Offset(0, 4)
                    .Value = .Value
                End With
            End If
        Next rng
    End With
End Sub

    The code will run automatically each time you save the workbook (including when you close it and click Yes to the Save prompt).

HansVogelaar's avatar
HansVogelaar
MVP
Jul 25, 2022

Nishkarsh31 

In the Visual Basic Editor, double-click ThisWorkbook under Microsoft Excel Objects.

Copy the following code into the ThisWorkbook module:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rng As Range
    With Worksheets("Data")
        For Each rng In .Range(.Range("A2"), .Range("A1").End(xlDown))
            If rng.Value <= Date Then
                With rng.Offset(0, 4)
                    .Value = .Value
                End With
            End If
        Next rng
    End With
End Sub

The code will run automatically each time you save the workbook (including when you close it and click Yes to the Save prompt).

  • Nishkarsh31's avatar
    Nishkarsh31
    Brass Contributor
    Jul 25, 2022

    Worked Like a charm. My life is so much at ease, Thank you so much

    1)Just out of curiosity, is there also a way, this inconsistent Table calculate formula can be removed using VBA Only.
    I know there's a setting for the same in the main settings,
    But I would need it just for this table. Active worksheet





    2)Also, in line 6
    We have manually written offset(0,4)
    But there are column additions in the table when the products are added,
    The header name "Amount" will remain same though

    How can we make it dynamic?

    P.s. The Date range would also remain the same only (A2:A)

    HansVogelaar 

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Jul 25, 2022

      Nishkarsh31 

      1) I don't think that's possible.

      2) This version will locate Amount dynamically:

      Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim rng As Range
    Dim c As Long
    With Worksheets("Data")
        c = .Range("1:1").Find(What:="Amount", LookAt:=xlWhole).Column - 1
        For Each rng In .Range(.Range("A2"), .Range("A1").End(xlDown))
            If rng.Value <= Date Then
                With rng.Offset(0, c)
                    .Value = .Value
                End With
            End If
        Next rng
    End With
End Sub
      • Nishkarsh31's avatar
        Nishkarsh31
        Brass Contributor
        Jul 25, 2022

         

        I was experimenting more with your code

        My Actual sheet has, 6000+ rows and 50 columns between
        Date and Amount
        When I ran the code, it kept on calculating threads and the workbook never closed





        However I also have a lot of other sheets in the same workbook,
        So I thought it must be because of that.

        But when I tried adding 6000+ rows, in the sample file, even it got delayed a considerable amount

        I am wondering if there's another approach, that will not slow down the workbook

        With my limited knowledge, I can think of two ways

        1) As suggested by PeterBartholomew1 , I use the xlookup to track the end of static range,
        Now it won't be completely apt, since the dates are not always added in chronological manner
        I can work around by reducing the static range by 30-40 rows and copying values

        2)Is there any way, that in Amount column, it can identify the last copied (non formula) row, so in every workbook close, it doesn't have to loop through the entire date range, to compare the dates?
        We have like 30 entries per day, so by 7 month, the rows are close to 30 x 7 x 30 = 6000+
        and it will keep expanding
        So it's better to have 30 rows copied and value pasted each day, rather than looping 6000 every time

        HansVogelaar 

Resources