Macro wont work with a cell which is updateing by formular

[m.paterson1]

I have the below Macro I have been using to up show the previous cells data and it has been working well until I updated my sheet so the cell updates by using a formula instead of manually, now the macro dose not work.


Private Sub Worksheet_Change(ByVal Target As Range)
Dim cng As String
Application.EnableEvents = False
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Range("A:A"), Target) Is Nothing Then
cng = Target.Value
Application.Undo
Target.Offset(0, 1) = Target.Value
Target = cng
End If
Application.EnableEvents = True
End Sub

Please Help.

[Kenneth Hobs]

Use Precedents.

[snb]

formulas won't trigger an event.

[Kenneth Hobs]

formulas won't trigger an event. which is why I said use precedents.

[m.paterson1]

I'm new to VBA, would it be possible to give me an example of using precedents? or even better edit the code?

[Kenneth Hobs]

Without knowing your data I guess:

If Not Intersect(Range("A:A").Precedents, Target) Is Nothing Then

[Paul_Hossler]

Clever -- never know about using .Precedents that way

[m.paterson1]

Thanks for the help so far, but I think I've taken on a larger project than I first thought, I have tried to implement the above code change with no luck. I am hoping you can have a look at the attached sample and point me in the right direction. 16274

I have the current Macro running but you will see that it dose not work with a formula updating it.

[Kenneth Hobs]

Use Cells(Target.Row, "G") sort of thing?

[m.paterson1]

I'm still not getting this to work by using the above, just run time error. I have also tried playing around with the below similar code, but once again it only works with manually updating the number and not by the formula updating the number.
If I cant get the below to work it there a Macro to copy the cell info from column AD and past as values in AC when a change is detected?



Private Sub Worksheet_Change(ByVal Target As Range)
Dim newVal As Variant
' Code won't work if multiple cells have been changed
If Target.Count > 1 Then
Exit Sub
End If
' Only act if cell in column AC has been changed
If Intersect(Range("AD:AD"), Target) Is Nothing Then
Exit Sub
End If
' Turn off events and screen updating
Application.EnableEvents = False
Application.ScreenUpdating = False
' Get new value
newVal = Target.Value
' Undo to restore old value
Application.Undo
' Copy old value to adjacent cell
Target.Offset(0, 1).Value = Target.Value
' Redo the change
Target.Value = newVal
' Turn on screen updating and events
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

[SamT]

The Post Editor Menu Bar # Icon will put CODE Tags around selected text.

[mdmackillop]

I've looked at your workbook but can't follow what data you are inserting and the logic which should follow. Can you explain further? Can an ID number appear more than once in sheets 1-4? If not, how do you handle this?

[m.paterson1]

The ID number can double up on in 1-4 but this will be managed by multiple work sheets and even out over time. I realise this is not perfect but its better that what we are currently using.
Basically I need to know what the last value in cell H was and put in G when cell H updates. So if no one has the tool then I will know who had last or at least the date it was last seen (Which will update when the name cell changes)

so H needs to = previous value of G when H is updated by the formula =SUMIF(C6:F6,"<>#N/A").
To be honest I thought this would be easy when I started but have quickly realised this is over my head.

[mdmackillop]

Setting aside your methodology
1 You enter an ID number in sheets 1-4
2 From this entry you wish to record the foreman's name and site
3 You also wish to record the date/time the data was entered.
Is this correct?

[m.paterson1]

that's correct. The only other item is that if the no one has the tool I want to remember who last had, which is why I was trying to remember the last number, where each person was assigned a number

[mdmackillop]

I think a different methodology is required. I'll look at this again tomorrow.

[SamT]

Incomplete code
Dim OldH As Somethig


Sub Sheet_SelectionChange(Target As Range)
If Not Intersect(Target, Range(You Know)) Is Nothing then OldH = Range(H)
End Sub

Sub Sheet_Change(Target blah blah)
If Range(H) <> OldH then G = H
End Sub