VBA Loop with Range monitor

[Aamati]

Hi all apologies, this is my first post so bear with me!
I'm running a macro in Excel which monitors values in a range of cells (say for arguments sake B2:C13) and if it sees any change in the cell, it will run another sub to copy the former values into a column (imagine it is for tracking stock prices)

in essence something like this:

Sub QuickOnTime()
  Dim i        As Long
  Do                                                       'infinite loop
    For i = 1 To 1000000                                   ' Start loop.
      If Not bLoop Then Exit Sub
      'Range("G1") = Timer                                  'normally your RTD changes G1, this is just for the test, remove this row !!!!!
      If Range("B2").Value <> OldValue Or Range("C2").Value <> OldValue1 Then
        CopieValues
        OldValue = Range("B2").Value                       'save new value of G1
        OldValue1 = Range("C2").Value
      End If
      If Range("B3").Value <> OldValue3 Or Range("C3").Value <> OldValue4 Then
        CopieValues1
        OldValue3 = Range("B3").Value                       'save new value of G1
        OldValue4 = Range("C3").Value
      End If
    If i Mod 10 = 0 Then                                 ' If loop has repeated 100 times.
        DoEvents                                           ' Yield to operating system.
      End If
    Next i
  Loop
End Sub

The code above then runs the following separate macros

Sub CopieValues()
  Dim lRij     As Long
  lRij = Range("L" & Rows.count).End(xlUp).Row + 1         'last row with data
  Range("L" & lRij).Resize(, 2).Value = Range("B2:C2").Value  'copy new values under last row
  Range("K" & lRij).Value = Now()                          'ev. save time
End Sub
Sub CopieValues1()
  Dim lRij     As Long
  lRij = Range("O" & Rows.count).End(xlUp).Row + 1         'last row with data
  Range("O" & lRij).Resize(, 2).Value = Range("B3:C3").Value  'copy new values under last row
  Range("N" & lRij).Value = Now()                          'ev. save time
  End Sub

This works well - however, if I was to extend my data set, would there be a way of automating the "OldValue"s above? that are currently set using:

Public OldValue As Variant
Public OldValue1 As Variant

Or any other suggestions to be able to monitor a range and see if it changes to then run a code to copy across just that one cell or row into a specified column (i.e. as you can see the CopieValues macros all reference different columns)


Let me know if anyone has any good ideas or can help me out with my project.

Thanks in advance!

A

 

[Hermanito]

While in that infinite loop, how can any of the monitored values ever change? Doesn't this prevent the user from interacting with the worksheet?

I think you should search for the Worksheet_Change event, there are plenty of topics about that. Also, recently I have seen a neat solution to do what you wanna do (I think) by using the _Change event in combination with the Undo method to capture the old values... searching for topics with 'Worksheet_Change' and 'Undo' in the body should reveal that topic...

 

[Aamati]

Thanks Hermanito -the range i'm using pulls data from a primary source which automatically refreshes.
What I am doing is having the code do a "log" to then be able to graph it out.
The Workbook_Change (to my knowledge) doesn't allow to specify different code when a single cell has changed, hence this use.

I guess the fundamental question to solving my problem is whether i can run the If functions and subsequently the Copievalue macro to the point where a range of (x, y) size has data.

I.e. instead of having to specify all the different OldValues i'd want it to do something like

If cells(2,2) changes, run CopieValues to paste to cells(x,y) where x is the last row in column y and column y is defined by what the original cells(2,2) represented - so as to match it.

Then following, i'd have if cells(2+1,2) change run copievalues to paste to (x,y+2) etcetc.

It's quite hard to explain so will try and get an example worksheet if that'd be easier.....

Thanks ,
A

Do you think it would be possible?

 

[Hermanito]

The Workbook_Change (to my knowledge) doesn't allow to specify different code when a single cell has changed, hence this use.

You can build a Select Case construction inside the Change event to do this, I don't really see the problem there.

This external source, how often does it refresh the data? You might run into performance issues if the data refreshes too fast...

Personally I'd look into the source side: isn't it possible to have the source output its data in a different manner, like a csv-file, and then afterwards you can parse that data?

An example would help clarify things, because I don't see the full picture yet, but I'm inclined to say it's possible (almost anything is possible in Excel )