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

 

[shamsu203]

HI
thanks for ur prompt reply and suggestions . Your idea of a marker against the latest column sounds great. Pls let me have the macro for this
You have been very helpful and ur helping me solve this is very much appreciated
Shamsu

 

[Yongle]

I suggest using the first empty row below the data for this
Do you want the marker VISIBLE so that anyone looking at the sheet can see it?

 

[shamsu203]

I am fine with ur suggestion about the row . Yes the marker be visible to all who see the sheet
Tks

 

[Yongle]

Sub ConfirmValues()
    Const firstR = 2, firstC = 3
    Dim c As Long, rowCount As Long, ws As Worksheet, cel As Range, x As String
    Set ws = ActiveSheet
    x = ChrW(10177)
    rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row - firstR
    
    For c = firstC To ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
        Set cel = ws.Cells(firstC, c).Offset(rowCount)
        If cel <> x Then
            If ws.Range("J1") - 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
                cel = x
                cel.HorizontalAlignment = xlCenter
            End If
        End If
    Next c
End Sub

 

[shamsu203]

Hi
I deleted the autorun macro and the one for value change then i copied pasted ur new vba. in a new module . when i run the property of the worksheet when I change the date and run the macro the formulas are removed and the value shown which is great. but the following error message is shown
Run time error"1004"
unable to get the EOmont property of the worksheet function class.
Pls advice.
Also should I use the autorun macro along with this
Sorry to trouble you but I really appreciate your help in this regard

 

[Yongle]

unable to get the EOmont property of the worksheet function class.

Sounds like VBA is not accepting that one of the values is a date.
Most likely, either a date is not where VBA is looking or else VBA is not interpreting the value as a date

This is the line that is failing, so we need to test the values before that happens
If ws.Range("J1") - WorksheetFunction.EoMonth(ws.Cells(firstR, c), -1) > 367 Then

Look at the immediate window in VBA after running this amended procedure and that should tell us what we need to know
When in VBA editor display immediate window with {CTRL} g
The output will look like this
J1 12/03/2020 7 < is that 7?
C2 01/04/2008 7 < is that 7?
J1 12/03/2020 7 < is that 7?
D2 01/04/2009 7 < is that 7?
J1 12/03/2020 7 < is that 7?
E2 01/04/2010 7 < is that 7?

Look at the cell references - are they correct?
If the cells are correct then there is at least one cell containing something that is not a date
Let me know

Sub ConfirmValues()
Dim dTest As Variant, dCel As Range, celTest As Range, celValueTest As Variant
    Const firstR = 2, firstC = 3
    Dim c As Long, rowCount As Long, ws As Worksheet, cel As Range, x As String
    Set ws = ActiveSheet
    x = ChrW(10177)
    rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row - firstR
 
    For c = firstC To ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
        Set cel = ws.Cells(firstC, c).Offset(rowCount)
        If cel <> x Then
     
On Error Resume Next
Set celTest = Nothing
Set dCel = Nothing
dTest = "D"
celValueTest = "V"
Set dCel = ws.Range("J1")
dTest = dCel.Value
Debug.Print dCel.Address(0, 0), dCel.Value, VarType(dTest), "< is that 7?"
Set celTest = ws.Cells(firstR, c)
celValueTest = celTest.Value
Debug.Print celTest.Address(0, 0), celTest.Value, VarType(celValueTest), "< is that 7?"

            If ws.Range("J1") - WorksheetFunction.EoMonth(ws.Cells(firstR, c), -1) > 367 Then
If Err.Number <> 0 Then Debug.Print Err.Number & vbCr & Err.Description

On Error GoTo 0
                ws.Cells(firstR + 1, c).Resize(rowCount).Value = ws.Cells(firstR + 1, c).Resize(rowCount).Value
                cel = x
                cel.HorizontalAlignment = xlCenter
            End If
        End If
    Next c
End Sub

 

[shamsu203]

Hi
Tks for ur reply. My knowledge of vba is limited and am unable to understand exactly what to do. Can I send u copy of the worksheet for u to check and if so how do I attach the worksheet to
the mail
Regrds
Shamsu

 

[Yongle]

No - I never download files

Are you saying that you cannot get the Immediate Window to display ?
- here is another way

The immediate window will look something like this and after you run the code will contain the details indicated in my previous post

 

[shamsu203]

hi
I give below the report generated
J1 13-Mar-20 7 < is that 7?
N2 01-Apr-19 7 < is that 7?
J1 13-Mar-20 7 < is that 7?
O2 8 < is that 7?
1004
Unable to get the EoMonth property of the WorksheetFunction class
J1 13-Mar-20 7 < is that 7?
P2 8 < is that 7?
1004
Unable to get the EoMonth property of the WorksheetFunction class
J1 13-Mar-20 7 < is that 7?
Q2 8 < is that 7?
1004
Unable to get the EoMonth property of the WorksheetFunction class
J1 13-Mar-20 7 < is that 7?
R2 8 < is that 7?
1004
Unable to get the EoMonth property of the WorksheetFunction class
J1 13-Mar-20 7 < is that 7?
S2 8 < is that 7?
1004
Unable to get the EoMonth property of the WorksheetFunction class
J1 13-Mar-20 7 < is that 7?
T2 8 < is that 7?
1004
Unable to get the EoMonth property of the WorksheetFunction class
J1 13-Mar-20 7 < is that 7?
U2 8 < is that 7?
1004
Unable to get the EoMonth property of the WorksheetFunction class
J1 13-Mar-20 7 < is that 7?
V2 8 < is that 7?
1004
Unable to get the EoMonth property of the WorksheetFunction class
J1 13-Mar-20 7 < is that 7?
W2 8 < is that 7?
1004
Unable to get the EoMonth property of the WorksheetFunction class
J1 13-Mar-20 7 < is that 7?
X2 8 < is that 7?
1004
Unable to get the EoMonth property of the WorksheetFunction class
J1 13-Mar-20 7 < is that 7?
Y2 8 < is that 7?
1004
Unable to get the EoMonth property of the WorksheetFunction class
the row o2 to y2 are showing no dates as there is formula to populate the cell if difference between n2 and current date is equal to or greater then 12. once this cell populated the other cells o3 and others get the value for that period.
waiting your reply
Shamsu

 

[Yongle]

The code below stops looping when a date is not found in row 2

Sub ConfirmValues()
    Const firstR = 2, firstC = 3
    Dim c As Long, rowCount As Long, ws As Worksheet, cel As Range, x As String
    Set ws = ActiveSheet
    x = ChrW(10177)
    rowCount = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row - firstR
    
    For c = firstC To ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
        Set cel = ws.Cells(firstC, c).Offset(rowCount)
        If cel <> x Then
            On Error Resume Next
            If ws.Range("J1") - WorksheetFunction.EoMonth(ws.Cells(firstR, c), -1) > 367 Then
                If Err.Number <> 0 Then Exit For
                On Error GoTo 0
                ws.Cells(firstR + 1, c).Resize(rowCount).Value = ws.Cells(firstR + 1, c).Resize(rowCount).Value
                cel = x
                cel.HorizontalAlignment = xlCenter
            End If
        End If
    Next c
End Sub