Real-Time Data Collection Question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to to the following:

I have 2 columns of data. Column 1 is a list of Stocks.
Column 2 is an associated column of data that is updating
in real-time. The value can be YES or NO. The value
starts at NO and can change back to NO very quickly.

Over the course of a 10 minute interval, I would like to
identify all Stocks in Column 1 that had a value of YES
in Column 2.

Regards and thanks you for your ideas.
 
Your persistence is impressive :)

Put the following in the worksheet's code module:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim aCell As Range
On Error Resume Next
For Each aCell In Target
If aCell.Column = 2 And Not aCell.HasFormula _
And UCase(aCell.Value) = "YES" Then
Application.EnableEvents = False
aCell.Offset(0, 1).Value = "Changed"
Application.EnableEvents = True
End If
Next aCell
End Sub

The code assumes column C is empty. For any 'yes' value in column B,
the corr. cell in column C will contain 'changed'.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Tushar, can I ask two questions please:

First: Not aCell.HasFormula

I can see what it does, but I am not clear why you need it?

Second, does the worksheet_change event always pick up any changes that
occur if a value changes through recalculation?

What I have in mind here is that the OP did not say what causes the value in
the second column to change to yes. Perhaps column 2 contains links to
another spreadsheet or a formula which changes when something else changes?

Thanks for any light you can shed on this.

Geoff
 
First: Not aCell.HasFormula

I can see what it does, but I am not clear why you need it?
Click to expand...
It works around any potential problem where a formula yields the
literal 'yes'.
Second, does the worksheet_change event always pick up any changes that
occur if a value changes through recalculation?
Click to expand...
No. It does pick up updates from an external link. To follow through
after every recalculation, use a Calculate event procedure.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Tushar Mehta said:
It works around any potential problem where a formula yields the
literal 'yes'.

No. It does pick up updates from an external link. To follow through
after every recalculation, use a Calculate event procedure.
Click to expand...

Thanks, Tushar
 
You are welcome. Glad to be of help.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

{snip}
 
Back
Top