VBA Macro with variable parameters ?

[StanSz]

I have a workbook with data indicating various conditions having been met. I use a 1 in the cell to indicate that the conditions have been met, and the cell is left blank if the conditions have not been met.

There are some situations which result in Circular Errors.

I want to use a macro to copy the data from one column to another, so that I can avoid the circular error, by having one formula check the original column, while another formula checks the copy.

If the workbook calculates and gives results in Column T, for example, I want to copy over the results from Column T into Column E.

How can I write the macro to allow for an expanding worksheet, when rows are added. I can write the macro so that Cells T31 to T520 are copied into cells E31 to E520, but how do I allow for a new row being entered at row 31, which would push the old row 31 down into row 32, and push the old row 520 down to 521 ??

Also, is there a way to write the macro to allow for the possibility of Columns being added or deleated ?? Perhaps using some variation of a MATCH command (matching the column headings for one possibiliby) ??

I want to have the data which is currently in Column T always be copied over into what is currently Column E. Even if columns are added or deleated I want the old Column T to be copied to the old Column E.

Any suggestions ??

Am I correct in thinking that this macro should be placed as Worksheet_Calculate ??

Thanks

StanSz

 

[Tazguy37]

What's the sheet look like? Post a sample for us if you can, please?

It depends on how the data is laid out, whether VBA can figure out where things go.

It could be a Calculate() event, or a Change() event. It depends on when you want the code to execute.

 

[StanSz]

I've got a sample to post, but I've never used Colo's HTML maker before for posting a ss.

I find that both version 2.42, and version 2.41 give me an error 91 message upon opening with macros enabled.

Is it me, or is there a problem there ??

StanSz

 

[StanSz]

OK, I figured it out:

Circular sample.adj3.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
29	Condition C checks for Condition T		1 = Condition		1 = Col T													Condition T checks for Condition C		1 = Condition
30			C triggers		has activated															T triggers
31	A	B	1
32					1													Q	R	1
33	A	B	1
34					1													Q	R	1
35	A	B	1															Q	R
36
37
38																		Q
39	A	B	1															Q	R
40					1													Q	R	1
41	A	B	1
42																		Q	R
43	A	B
44																		Q	R
45					1													Q	R	1
46	A	B	1
47	A																	Q	R
Sheet1

Condition T checks for the prior activation of Condition C in Column C -- all's OK. Condition C needs to check for the prior activation of Condition T. I can't check Column T directly otherwise I get a Circular Reference. My idea was to copy (via a VBA macro) the results in Column T, into Column E, and then have the formula in Column C check not the data in Column T, but the data in Column E, (as per C31) which was copied via a Macro and is identical to what is in Column T.

StanSz