Help request to change formula to value

[mso3]

Hi friends,
The code is working fine but I have following problems in it.
1. The code is running after clicking on any cells on the personal sheet.
2. The formula is not changing to value.
I want the value instead of formula.

Any help will be highly appreciated.

Thanking you in anticipation.

[jordan2322]

In point 1. you mention the code is "running after clicking on any of the cells".. your code is placed inside the Worksheet_Change event routine, is this not where you want it?

If not please clarify where you want it - OR - describe the operations that must occur in order for it to run.

Also, I would consider NOT using a mixture of VBA as well as worksheet functions.. it would be easier to manage if it were completely done in VBA.

Regards
Jordan

[mso3]

Hi,

I want to place the code in Worksheet_Change event to perform the action after entering the date in column 'P and AD' respectively only and not entring any data on the sheet in any cell.

Please suggest me an amendment to achieve the target automatically after entering the dates.

Thank you.

[alansidman]

I use the following to change formulas to hard coded data. You could incorporate this into your code

Sub copypastespecial()
    ActiveCell.Copy
    ActiveCell.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
End Sub

[mikerickson]

I use the following to change formulas to hard coded data. You could incorporate this into your code

Sub copypastespecial()
    ActiveCell.Copy
    ActiveCell.PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
End Sub

I prefer

ActiveCell.Value = ActiveCell.Value

since it doesn't fiddle with the clipboard.

[mso3]

Hi Alan,
Thank you. Sorry, I don't want to run code from standard module. It's not a question of copy/paste. I want the output after entering the dates in the cell automatically and change to value.

Please suggest me a correction in the code to achieve the target automatically. I need a sheet module code only.

Thank you.

[jordan2322]

You could encapsulate your worksheet_change code in the following way..

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 16 = P, 17 = Q, change these columns numbers to what you need
    If Target.Column = 16 Or Target.Column = 17 Then
        ' All your calculation code goes here
    End If
End Sub

Let me know if that helps

[mso3]

Hi,
I have made necessary changes but getting error 91.

Something is wrong in my coding. Sorry for not understanding your suggestion correctly.

Please do the necessary amendments in the code below to achieve the target.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim cell As Range
    ' 16 = P, 30 = AD
    If Target.Column = 16 Or Target.Column = 30 Then
        Application.EnableEvents = False
If Not Intersect(Target, Range("P:P")) Is Nothing Then
    For Each cell In Intersect(Target, Range("P:P"))
    If Target.Row < 9 Then Exit Sub
    If Target.Column <> 16 Then Exit Sub
        If cell.Value = "" Then
                    Cells(cell.Row, "W").Value = ""
Else
Cells(cell.Row, "W").Formula = _
"=IF(RC[-7]="""","""",IF(RC[-20]=""Duplicate"",""Duplicate"",IF(AND(RC[-7]=""-"",RC[-6]=""-""),""-"",IF(COUNT(RC[-7]:RC[-6])=1,""Sanctioned"",""Expired""))))"
'.Value = .Value
                    End If
        Columns.AutoFit
    Next cell
End If

'Remark 3

If Not Intersect(Target, Range("P:P")) Is Nothing Then
    For Each cell In Intersect(Target, Range("P:P"))
    If Target.Row < 9 Then Exit Sub
    If Target.Column <> 16 Then Exit Sub
If cell.Value = "" Then
                    Cells(cell.Row, "AG").Value = ""
Else
Cells(cell.Row, "AG").Formula = _
"=IF(RC[-10]=""Sanctioned"",""Send mail"",IF(RC[-10]=""Expired"",""Closed"",IF(RC[-10]=""-"",""Closed"",IF(RC[-10]=""Duplicate"",""Closed"",""""))))"
'.Value = .Value
                    End If
        Columns.AutoFit
    Next cell
End If
End If

'Remark2

If Not Intersect(Target, Range("AD:AD")) Is Nothing Then
    For Each cell In Intersect(Target, Range("AD:AD"))
    If Target.Row < 9 Then Exit Sub
    If Target.Column <> 30 Then Exit Sub
If cell.Value = "" Then
                    Cells(cell.Row, "AF").Value = ""
Else
Cells(cell.Row, "AF").Formula = _
"=IF(RC[-2]="""","""",IF(AND(RC[-2]=""-"",RC[-1]=""-""),""-"",IF(COUNT(RC[-2]:RC[-1])=1,""Send mail"",""Expired"")))"
'.Value = .Value
                    End If
        Columns.AutoFit
    Next cell
End If
    Application.EnableEvents = True
End Sub

Thank you.