convert formulas into values

[shamsu203]

HI
I have a vba which converts formulas into values . I want to know if it is possible for the macro to run on completion of a certain period say 12 month based on the date difference between todays date and the start date. the macro i use is given beow
Sub ConvertToValues()

Dim MyRange As Range

Dim MyCell As Range

Set MyRange = Selection

For Each MyCell In MyRange

If MyCell.HasFormula Then

MyCell.Formula = MyCell.Value

End If

Next MyCell

End Sub
tks for any suggestion you provide
shamsu

 

[Yongle]

1. Clicking on <vba/> allows you to paste your code inside code tags which makes it easier for others to read and understand

2. Try this alternative to your existing code (does the same thing in one line)

Sub ConvertToValues() 
   Selection.Value = Selection.Value
End Sub

3. Which cell contains the StartDate used to determine that data should be overwritten with values ?
- is every row being overwritten ?
- if not which cell in the row does VBA use to identify the correct rows (does that contain a date or something else)
- which column's formulas do you want to overwrite ?

 

[shamsu203]

hi
I attach copy of the work sheet for your info.
1) cell j1 contains the todays date and finds the difference from the date shown on row 2
2)yes all the rows in the concerned column is overwritten
3) for example if the no of months between the date is equal to 12 (eg if the dif between n2 and j1 =12 then all of n column should be rewritten
tks for your help
Shamsu

Menu

11-03-20​

PROPERTY TAXES

TOTAL

01-04-08​

01-04-09​

01-04-10​

01-04-11​

01-04-12​

01-04-13​

01-04-14​

01-04-15​

01-04-16​

01-04-17​

01-04-18​

01-04-19​

SELFI

₹ 30,324.00​

₹ 0​

₹ 0​

₹ 2,644​

₹ 2,644​

₹ 2,644​

₹ 3,614​

₹ 3,614​

₹ 3,614​

₹ 3,614​

₹ 3,616​

₹ 3,616​

704.00​

0.00​

0.00​

0.00​

0.00​

MAJOR

₹ 0.00​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

0.00​

0.00​

0.00​

0.00​

0.00​

GLOBAL GF

₹ 25,122.00​

₹ 0​

₹ 0​

₹ 1,984​

₹ 1,984​

₹ 1,984​

₹ 3,195​

₹ 3,195​

₹ 3,195​

₹ 3,195​

₹ 3,195​

₹ 3,195​

0.00​

0.00​

0.00​

0.00​

0.00​

GLOBAL 1

₹ 21,906.00​

₹ 0​

₹ 0​

₹ 1,404​

₹ 1,404​

₹ 1,404​

₹ 2,949​

₹ 2,949​

₹ 2,949​

₹ 2,949​

₹ 2,949​

₹ 2,949​

0.00​

0.00​

0.00​

0.00​

0.00​

GLOBAL 2

₹ 20,268.00​

₹ 0​

₹ 0​

₹ 1,854​

₹ 1,854​

₹ 1,854​

₹ 2,286​

₹ 2,286​

₹ 2,286​

₹ 2,286​

₹ 2,286​

₹ 2,286​

990.00​

0.00​

0.00​

0.00​

0.00​

GLOBAL 3

₹ 21,366.00​

₹ 0​

₹ 0​

₹ 1,686​

₹ 1,686​

₹ 1,686​

₹ 2,718​

₹ 2,718​

₹ 2,718​

₹ 2,718​

₹ 2,718​

₹ 2,718​

0.00​

0.00​

0.00​

0.00​

0.00​

CAPITAL MALL

₹ 67,493.00​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 10,390​

₹ 11,571​

₹ 17,341​

₹ 11,355​

16836.00​

0.00​

0.00​

0.00​

0.00​

TOTAL TAXES

₹ 186,479.00​

₹ 0.00​

₹ 0.00​

₹ 9,572.00​

₹ 9,572.00​

₹ 9,572.00​

₹ 14,762.00​

₹ 14,762.00​

₹ 25,152.00​

₹ 26,333.00​

₹ 32,105.00​

₹ 26,119.00​

₹ 18,530.00​

₹ 0.00​

₹ 0.00​

₹ 0.00​

###​

MAINTENACE & REPAIR

SELFI

₹ 0.00​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

0.00​

0.00​

0.00​

0.00​

0.00​

MAJOR

₹ 0.00​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

0.00​

0.00​

0.00​

0.00​

0.00​

GLOBAL GF

₹ 28,070.00​

₹ 0​

₹ 0​

₹ 12,851​

₹ 1,691​

₹ 1,691​

₹ 1,691​

₹ 1,691​

₹ 1,691​

₹ 1,691​

₹ 1,691​

₹ 1,691​

1691.00​

0.00​

0.00​

0.00​

0.00​

GLOBAL 1

₹ 21,560.00​

₹ 0​

₹ 0​

₹ 11,039​

₹ 1,169​

₹ 1,169​

₹ 1,169​

₹ 1,169​

₹ 1,169​

₹ 1,169​

₹ 1,169​

₹ 1,169​

1169.00​

0.00​

0.00​

0.00​

0.00​

GLOBAL 2

₹ 16,704.00​

₹ 0​

₹ 0​

₹ 8,424​

₹ 920​

₹ 920​

₹ 920​

₹ 920​

₹ 920​

₹ 920​

₹ 920​

₹ 920​

920.00​

0.00​

0.00​

0.00​

0.00​

GLOBAL 3

₹ 10,611.00​

₹ 0​

₹ 0​

₹ 4,139​

₹ 719​

₹ 719​

₹ 719​

₹ 719​

₹ 719​

₹ 719​

₹ 719​

₹ 719​

720.00​

0.00​

0.00​

0.00​

0.00​

CAPITAL MALL

₹ 0.00​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

₹ 0​

0.00​

0.00​

0.00​

0.00​

0.00​

TOTAL

₹ 76,945.00​

₹ 0.00​

₹ 0.00​

₹ 36,453.00​

₹ 4,499.00​

₹ 4,499.00​

₹ 4,499.00​

₹ 4,499.00​

₹ 4,499.00​

₹ 4,499.00​

₹ 4,499.00​

₹ 4,499.00​

₹ 4,500.00​

₹ 0.00​

₹ 0.00​

₹ 0.00​

###​

TOTAL EXPENSES

₹ 263,424.00​

₹ 0.00​

₹ 0.00​

₹ 46,025.00​

₹ 14,071.00​

₹ 14,071.00​

₹ 19,261.00​

₹ 19,261.00​

₹ 29,651.00​

₹ 30,832.00​

₹ 36,604.00​

₹ 30,618.00​

₹ 23,030.00​

₹ 0.00​

₹ 0.00​

₹ 0.00​

###​

 

[Yongle]

Test on a COPY of your data

Everything in the column is overwritten including total formula
VBA function DATE is the same as Excel function TODAY()
If you prefer to use the value in J1 , amend one line
If ws.Range("J1") - WorksheetFunction.EoMonth(ws.Cells(firstR, c), -1) > 367 Then

Sub ConfirmValues()
    Const firstR = 2, firstC = 3
    Dim c As Long, rowCount As Long, ws As Worksheet
    Set ws = ActiveSheet
    rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row - firstR
   
    For c = firstC To ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
        If Date - WorksheetFunction.EoMonth(ws.Cells(firstR, c), -1) > 367 Then
            ws.Cells(firstR + 1, c).Resize(rowCount).Value = ws.Cells(firstR + 1, c).Resize(rowCount).Value
        End If
    Next c
End Sub

 

[shamsu203]

HI
Thks for ur prompt reply. I would prefer to use the value in j1 as this will give me the chance to check if the vba is working by changing the date. As suggested by you I changed the 7th line to
if ws.Range("J!")-WorksheetFunction.EOmonth(ws.Cells(firstR,c),-1)>367 Then
I get
run time error"1004"
unable to get the EO month property of the worksheet Function class
Pls advice

 

[shamsu203]

sri further would I have to run the macro or on the due date will the formulas be cleared automatically

 

[Yongle]

HI
Thks for ur prompt reply. I would prefer to use the value in j1 as this will give me the chance to check if the vba is working by changing the date. As suggested by you I changed the 7th line to
if ws.Range("J!")-WorksheetFunction.EOmonth(ws.Cells(firstR,c),-1)>367 Then
I get
run time error"1004"
unable to get the EO month property of the worksheet Function class
Pls advice

There is an error in the range
BAD
J!
GOOD
J1

 

[Yongle]

further would I have to run the macro or on the due date will the formulas be cleared automatically

what do you want to happen ?


To automate it so that the macro is run every time the workbook is opened ....
Place code in ThisWorkbook code window (not Module1, Module2)

Private Sub Workbook_Open()
    Call ConfirmValues
End Sub

 

[shamsu203]

hi
I had used J1 and not J! when changing the line . Pls advice . I want the macro to run when when the 12 month period is over. Will the code work accordingly

 

[Yongle]

The code in post#8 does that every time the workbook opens
- it runs the code
- any columns that match the criteria are overwritten with values
- it overwrite the values in every matching column evey time the workbok is opened
- whilst unnecessary, it does no harm either

A marker could be placed against the latest column overwritten with values to tell VBA only to consider later columns

Let me know