worksheet change
Hi there,
I have a couple of questions regarding the worksheet_change function. I have
a process modelling software that prints out results at certain times to an
Excel worksheet. When a new value is received by excel I am using a
worksheet_change macro to perform some other calculations. At the moment I am
triggering these calculations by checking if the value of certain cells have
changed to a specific value using an if loop like this:
Dim time1 As Integer
time1 = 1500
If (Cells(1, 1).Value = time1) And (Cells(1,7) < 0) Then
....{some code}
End If
Once the calculations have been performed, Excel will receive a new value
from the other software. The new value is sent to the cell below the previous
cell, so in this case to cell (2,1). This makes my code very inconvenient
since I have to repeat my if loop like this:
If (Cells(2, 1).Value = time2) And (Cells(2,7) < 0) Then
....{some code}
End If
I have tried creating a counter that will increase everytime a new value is
received by excel so that
Dim counter as integer
counter = 1
If (Cells(counter, 1).Value = time2) And (Cells(counter,7) < 0) Then
....{some code}
counter = counter+1
End If
but it seems to reset itself everytime the if loop starts. How can I make
the counter keep track of the number of times the if loop has been used and
only initialise it when the workbook is reopened rather than everytime the
macro is used?
Thank you
|