In desperate need of help!

[breakbacktennis]

Hi, Im in desperate need of help with this VBA code. The code works fine when entered manually however I need the code to work when linked with a live feed.

[COLOR=#333333]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR]    Dim KeyCells As Range




   If Target.Address <> Range("G1").Address Then Exit Sub
    


        If Range("G1").Value = "In-play" Then
               Range("U9").Value = Range("G9").Value
               Range("U11").Value = Range("G11").Value
        End If

 [COLOR=#333333]End Sub[/COLOR]

 

[CodeNinja]

So it sounds like it is working as programmed... it's just that you only want to keep the first transition from not in play to in play... does that sound correct?

 

[breakbacktennis]

So it sounds like it is working as programmed... it's just that you only want to keep the first transition from not in play to in play... does that sound correct?

Spot on Ninja, when In-play appears the values that should appear do but then they are refreshed, so it is working but i just want that first transition as u state.

 

[CodeNinja]

If you don't want to have it continue to update, simply take out the else condition that resets the Boolean:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
    If Range("G1").Value = "In-play" And bIsInTarget = False Then
           Range("U9").Value = Range("G9").Value
           Range("U11").Value = Range("G11").Value
           bIsInTarget = True
    End If
    
Application.EnableEvents = True
End Sub

That should fix it...

Good luck,

CN.

*** Post Note: If you close the workbook and re-open, it will again update. If you don't want that to happen, then you need to change the Boolean to something like have I updated today... Store the last update date somewhere and set your Boolean by that value as compared to today(). If you want that, let me know... ***

 

[breakbacktennis]

If you don't want to have it continue to update, simply take out the else condition that resets the Boolean:

Private Sub Worksheet_Calculate()
Application.EnableEvents = False
    If Range("G1").Value = "In-play" And bIsInTarget = False Then
           Range("U9").Value = Range("G9").Value
           Range("U11").Value = Range("G11").Value
           bIsInTarget = True
    End If
    
Application.EnableEvents = True
End Sub

That should fix it...

Good luck,

CN.

*** Post Note: If you close the workbook and re-open, it will again update. If you don't want that to happen, then you need to change the Boolean to something like have I updated today... Store the last update date somewhere and set your Boolean by that value as compared to today(). If you want that, let me know... ***

Well CN i cant believe uv not had enough cus I have lol. Still not working. Works when i manually input In-play though , just cant seem to get what it is. You have been a massive help though CN