problem with code

[davez]

Hi, I have encountered a problem, hoping someone can help

In cell HM3 i have Gary's countdown formula modified to my needs -

=IF(LEFT(GW4)<>"-",(HOUR(GW4)*3600)+(MINUTE(GW4)*60)+SECOND(GW4),-((HOUR(SUBSTITUTE(GW4,"-",""))*3600)+(MINUTE(SUBSTITUTE(GW4,"-",""))*60)+SECOND(SUBSTITUTE(GW4,"-",""))))

& the following worksheet change code -

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address <> "$HM$3" Then Exit Sub
    Application.EnableEvents = False
   
    With ThisWorkbook.Sheets(Target.Worksheet.Name)
    If .Range("$HM$3") < 5 Then
   
    Range("HJ7:HR30").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("HL5").Select
   
    End If
    End With
 
    Application.EnableEvents = True
End Sub

now nothing happens when the value in HM3 counts down to less than 5 seconds, but if i manually type a number less than 5 into the cell then the copy/paste action takes place.

any ideas why the code wont run? many thanks if anyone can help.

[GaryRussell]

Worksheet_Change is fired by the value changing when written to by VBA, manual input or BA writing to the worksheet. It is not fired by a formula changing the value. This is why it works when you manually enter a value.

You can use the Worksheet_Calculate event instead so it would read as follows.

Code: Select all

Private Sub Worksheet_Calculate()

    Application.EnableEvents = False
   
    With ThisWorkbook.Sheets(Target.Worksheet.Name)
    If .Range("$HM$3") < 5 Then
   
    Range("HJ7:HR30").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("HL5").Select
   
    End If
    End With
 
    Application.EnableEvents = True
End Sub


[davez]

Thanks Gary, I have made the change to Calculate but now have another problem - when opening the workbook I get a 'runtime error 424 - object required' message with the following code highlighted -

With ThisWorkbook.Sheets(Target.Worksheet.Name)


Thanks if can assist any further

[GaryRussell]

Sorry, Target is not passed to the calculate event.

It should read as follows.

Code: Select all

Private Sub Worksheet_Calculate()

    Application.EnableEvents = False
   
    If Range("$HM$3") < 5 Then
   
    Range("HJ7:HR30").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("HL5").Select
   
    End If
    End With
 
    Application.EnableEvents = True
End Sub


I'm not convinced it's going to do what you want though, I tried this code and it doesn't appear to do anything useful. I am guessing you want to copy a range of cells to another location in the worksheet, but it doesn't appear to do that. If you could describe exactly what you want it to do I could post some better code. This code looks like it was recorded as a macro. It can be made a lot simpler.

[davez]

Hi Gary, what I want to do with this code is to copy/paste special/values a range that contains formulas so that from the 5 second point that range is no longer changed.

Once I had worked that out I was hoping to create code that would copy the back odds at differing time points, for example at 300 seconds, 120 seconds, 60 seconds and paste those values to an area whereby I could then analyze their movements.

To explain further, I am hoping to create code that will paste the 300 second back odds from their current position at GY7:GY30 to JA7:JA30, the 120 seconds odds to JB7:JB30, the 60 seconds odds to JC7:JC30.

Again, any help you can provide is very much appreciated.

[osknows]

Hi davez,

I think this will do what you need as long as your refresh rate is less than a second to catch

Code: Select all

Private Sub Worksheet_Calculate()

    Application.EnableEvents = False
   
   
    With Sheet1  'change to required sheet
    Select Case .Range("$HM$3").Value
   
    Case 300  'could use Case 300 to 295 to introduce 5 second window
    .Range("JA7:JA30").Value = .Range("GY7:GY30").Value
   
    Case 120
    .Range("JB7:JB30").Value = .Range("GY7:GY30").Value
   
    Case 60
    .Range("JC7:JC30").Value = .Range("GY7:GY30").Value
   
    Case Else
    'do nothing
   
   
    End Select
    End With
 
    Application.EnableEvents = True
End Sub


[davez]

cheers osknows, will give it a run today

[davez]

thanks for replies guys, unfortunately I cant get any to activate once required seconds are reached

[osknows]

I think you need to be clear how you intend to trigger your events

Worksheet_Change(byval Target as range) - this event runs after a cell or cells are changed in a worksheet

Worksheet_calculate() - this event runs after a calculation, recalculation or any cells which affect the calcluation on a sheet changes.

Genrally the Worksheet_change event is the one you need if using on the same sheet that BA updates on.

Code: Select all

Private Sub Worksheet_Change(byval Target as range)
If Target.Columns.Count <> 16 Then Exit Sub

    Application.EnableEvents = False
   
   
    ThisWorkbook.Sheets(Target.Worksheet.Name) 
    Select Case .Range("$HM$3").Value
   
    Case 300  'could use Case 295 to 300 to introduce 5 second window
    .Range("JA7:JA30").Value = .Range("GY7:GY30").Value
   
    Case 120
    .Range("JB7:JB30").Value = .Range("GY7:GY30").Value
   
    Case 60
    .Range("JC7:JC30").Value = .Range("GY7:GY30").Value
   
    Case Else
    'do nothing
   
   
    End Select
    End With
 
    Application.EnableEvents = True
End Sub