Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change the pivot chart automaticaly as values in the worksheet change | Excel Worksheet Functions | |||
change formula in a shared worksheet without losing change history | Excel Worksheet Functions | |||
Change in one Worksheet Activates Another Worksheet Change | Excel Programming | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Change Cell from Validated List Not Firing Worksheet Change Event | Excel Programming |