troubleshooting Question

Help capturing RediPlus RediLink tics; Worksheet_Change not working

Avatar of mwmosu
mwmosu asked on
Microsoft Excel
2 Comments1 Solution1002 ViewsLast Modified:
Hello,

I am using RediPlus (trading software), and specifically, it's add-in to Excel.  It allows me to display stock data (in my case, volume and last traded price of a certain symbol) in an assigned cell.  The data is continually updated as stocks, etc, are traded, real time.  Needless to say, this data can change very quickly.  

I am trying to grab each instance of data and copy and paste it into a column: in other words, trying to capture each 'tic' in an Excel column.  Originally, I set up the VBA code as seen below; pretty simple (snippets and clips from various places) to instigate from the Worksheet_Change event.

My issue has become (I believe) that the spreadsheet doesn't recognize that the data is changing.  My macro works when you type data in the target cells and press enter.  However, it doesn't when the live data is put into the cell.  The data changes and the macro is not set in motion by the Worksheet_Change event.

Also--check my code--maybe I wrote it wrong.  It's been awhile since I've programmed, so....

Thanks in advance--
Private Sub Worksheet_Change(ByVal Target As Range)
 
    setTimeMin = Application.Range("B2")
    setTimeMax = Application.Range("B3")
    
    If (Time < setTimeMax And Time > setTimeMin) Then
               
        If IsNumeric(Target) Then
            
            If Not Intersect(Target, Range("C2:D2")) Is Nothing Then
     
           On Error Resume Next
                Application.EnableEvents = False
                    
                rowCountA = Application.CountA(Range("C:C")) + 1
                Worksheets("Sheet1").Range("C2:D2").Copy
                ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("C" & rowCountA & ":D" & rowCountA)
                Worksheets("Sheet1").Range("E" & rowCountA).Value = Time
                Application.CutCopyMode = False
                
                Application.EnableEvents = True
 
            On Error GoTo 0
 
            End If
        End If
    End If
End Sub
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros