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

worksheet change
 

One option would be store the counter value in a cell and re-use that. Not
the most elegant.

Alternatively test for the last value in row using ....

counter = cells(rows.count,"A").End(xlup).Row

This will cause the counter to be set to the row number of last value in
column A, where the latest value appears.


--
Cheers
Nigel



"Chandee3" wrote in message
...
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