Need help on a macro which I put in worksheet change area.

[ghrek]

Hi

I have a workbook what had a formula in column E . What Im trying to do is when there is a data change in column E I need the data changed to values so it knocks out the formula on the cell where the data changed.

Any Ideas?

 

[James006]

Hi,
What you are aiming for is possible ... as long as you clearly determine the last input cell which modifies the result shown in Column E ...

 

[HaHoBe]

Hi ghrek,

to spot a formula I would expect you to use the Worksheet_Calculate-event as a formula will not trigger the Worksgheet_Change-Event AFAIK.

Ciao,
Holger

 

[Joe4]

Hi ghrek,

to spot a formula I would expect you to use the Worksheet_Calculate-event as a formula will not trigger the Worksgheet_Change-Event AFAIK.

Ciao,
Holger

That is correct.

The big drawback/issue with "Worksheet_Calculate" as opposed to "Worksheet_Change", is that "Worksheet_Calculate" can only determine that SOME cell somewhere on the sheet was re-calculated.
Unlike "Worksheet_Change", it cannot tell WHICH cell changed. However, "Worksheet_Change" does not trigger when values returned by formulas change.

 

[ghrek]

Right just so I understand if I use worksheet calculate that will do what I need it to ?

 

[Joe4]

Right just so I understand if I use worksheet calculate that will do what I need it to ?

I don't know. Your question is very vague.
You really have not explained exactly what you want to do in detail.

It would probably be best to show us an example and walk us through exactly what you want to happen.

 

[James006]

Did you read post # 2 ...?

Could you tell us which formula is located in Column E ... and more importantly since you are updating some cells manually which will impact the result calculated by your formula .... which cell is the very last one you do modify to see the final calculation in Column E ???

 

[ghrek]

on column E the formula is =SUM(G4-H4) all the way down the column in every row.

When im trying to copy and paste that column into a new worksheet it not having it and just says REF!

Tried to post a bit of worksheet but not letting me do it

 

[James006]

You can test

Private Sub Worksheet_Activate()
' To be stored in Sheet2 module
' Adjust your number of rows
 Range("E4:E50").Value = Sheet1.Range("E4:E50").Value
End Sub

 

[HaHoBe]

Hi ghek,

maybe have a go with this:

Private Sub Worksheet_Change(ByVal Target As Range)
' https://www.mrexcel.com/board/threads/need-help-on-a-macro-which-i-put-in-worksheet-change-area.1233386/
Dim rngFormulas As Range

If Target.Count = 1 Then
  On Error Resume Next
  Set rngFormulas = Range("E1", Cells(Rows.Count, "E").End(xlUp)).SpecialCells(xlCellTypeFormulas, 23)
  If Not rngFormulas Is Nothing Then
    If Not Intersect(Target, rngFormulas.Offset(0, 1).Resize(, 2)) Is Nothing Then
      Application.EnableEvents = False
      With Cells(Target.Row, "E")
        .Value = .Value
      End With
      Application.EnableEvents = True
    End If
  End If
End If

Err.Clear
On Error GoTo 0
Set rngFormulas = Nothing

End Sub

Ciao,
Holger