Take last non-blank cells in 2 dynamically updated sheets and run a basic calc

Question

0.00/5 (No votes)

See more:

Hello,

This is my first post on this site. I would like to request some help to code up a macro to do the following: I have two different real-time data feeds (via a DDE / ActiveX) that are going to 2 worksheets within the same book (say Sheet1 and Sheet2). What I need is:
1. Take the last non-blank cell in a given column (Column A in Sheet1 and Column B in Sheet 2) and
2. Do a basic calculation - divide the first value by the second value, which will be updated in the third worksheet (Sheet3)
3. Add logic that if the result from 2. (call it 'Ratio') is > a number (0.5), then set adjecent cell next to the 'Ratio' to '1', else set to '0'.
3. Repeat steps 1-3 in close to real-time (say every milli-second).

The hardest thing is to have this code running in a loop and the values in Sheet1 and Sheet2 are dynamically changing (close to real-time).

Andrew

Posted 1-Sep-11 8:48am

andtherus

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

santhoshkvijayan · Answer 1 · 2011-11-21T02:02:00

hi andtherus,
hope this solution helps you.
You can have this code under the Sheet2, so when ever a change happenes in the sheet the code is triggred automatically.

VB

Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet1").Activate
    If WorksheetFunction.CountA(Cells) > 0 Then
        Sheet1RowCount = ActiveSheet.UsedRange.Rows.Count
    End If
    If Sheet1RowCount = 0 Then
        Exit Sub
    End If
    Sheet1Lastdata = Worksheets("Sheet1").Cells(Sheet1RowCount, 1).Value

    Worksheets("Sheet2").Activate
    If WorksheetFunction.CountA(Cells) > 0 Then
        Sheet2RowCount = ActiveSheet.UsedRange.Rows.Count
    End If
    If Sheet2RowCount = 0 Then
        Exit Sub
    End If
    Sheet2Lastdata = Worksheets("Sheet2").Cells(Sheet2RowCount, 2).Value

    Worksheets("Sheet3").Activate
    If WorksheetFunction.CountA(Cells) > 0 Then
        Sheet3RowCount = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        Sheet3RowCount = Sheet3RowCount + 1
    End If
    DivValue = Round(Sheet1Lastdata / Sheet2Lastdata, 0)
    Worksheets("Sheet3").Cells(Sheet3RowCount, 1).Value = DivValue
End Sub