Using Macro2 to iteratively call a Worksheet_Change Sub

buffalo

Board Regular
Joined
Jun 19, 2003
Messages
183
Hi,

I have data in COl A thru I and caluculations in Col L thru R.

I enter new data in a new row under these columns A:I,

As soon as I enter new data and reach Col I (which is the last information entry), the Target.Column = 9 and triggers the code inside a Worksheet_change sub to alter ALL rows in Col L thru R

For e.g.

New entry in row 6 under Col A thru I will cause L1:R6 to change.
the Worksheet_change causes these changes, the code is in principle as below:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Row = 1 Then Exit Sub
    
    If Target.Column = 9 Then
    
    Dim Rng As Range
    Dim c As Range 

    Set Rng = Range("L2:L" & Range("L65536").End(xlUp).Row)
             
    For Each c In Rng

    	{Do something }
'This modifies all columns L:R and ALL rows.
' I think changng this range should do the job, but HOW?

    Next c

end Sub()

Now if one is to delete one of the rows, say row 4, the calculations columns will be a big mess (as they will include some part of the deleted entries)

I want to have a macro Macro2 to iteratively call Worksheet_change so that
the sheet "relives" and recalculates the WHOLE process again as if the deleted entry never existed.

So when the user deletes some column xyz, the data shifts up and the calculations are a mess.

I use the Macro2 (maybe using Ctrl + m) to recalculate everything after any row deletion is made.
the worksheet_change function is 'somehow' called and only row 1 is considered and calculation in L1:R1 is made.

Next the Macro2 calls worksheet_change again ... and entry in row 2 Col A thru I is considered as a new entry. Now L1:R2 will be recalculated.

goes on till the last entry row in A:I where L1:Rlastrow

...effectively a 'full run' calculating and recalculating:

for every row x in A:I ...
Recalculate L1:Rx

Maybe setting the ranges appropriately in the worksheet_change function and writing a Macro2 which calls the worksheet_change will do the job?

any suggestions are welcome.

thanks
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

jimboy

Well-known Member
Joined
Apr 11, 2002
Messages
2,314
What about adding this line to the bottom of Macro2 to force the worksheet_change event?

Code:
Range("I" & Range("i65536").End(xlUp).Row) = Range("I" & Range("i65536").End(xlUp).Row).Value
 

Watch MrExcel Video

Forum statistics

Threads
1,113,917
Messages
5,545,024
Members
410,647
Latest member
bernardazar
Top