Loop within Excel Formula?
 

Is there a way I can do a loop in an excel formula?
Is is possible to call a macro on a cell change event?
Is is possible to modify or add formulas to Excel?

When a formula is updated, I want a loop to fire off that updates the value
of the cell by appending a number to it:
essentially I want to automatically change a column without manually running
a macro. For example, I want the formula to make sure the value in a given
column is unique, so I would want to change:
1
1
1
1
1

to:
1
1_1
1_2
1_3
1_4

Any help is appreciated...
thanks, dan

Loop within Excel Formula?
 

You can run a macro/VBA code on a cell change event, but
you have to make sure that you don't get caught in an
infinite loop. Because everytime you change the cell to
look like you want, then you end up calling a cell change
event, to update the cell, that calls a cell change event,
to update the cell, etc.. etc.. etc.. So there has to be
some point of reference at the beginning of the function
that does the update to exit the update if the cell has
already been updated. For example, if you always know
that you will append _1 for the 2nd cell, _2 for the 3rd,
etc.. Then test the text to see if it's location
corresponds to the number added at the end. Ie. If Cell
1, 1 is changed
Cell 2, 1 is automatically updated with the data from Cell
1, 1 but with _1 added. So the test would be.. Does Cell
2, 1, have '_1', if so exit the update Or do the
comparison for the next cell, if not, update the cell.

Another thing you could do, would be to create all of the
updates in a separate worksheet... Then copy the updated
list to the current worksheet, and again you would still
need something to stop the cycle of updating, such as do
the target cells begin with the changed data.. If so, then
get out of the loop, if not, then make the list of data
and copy it to the appropriate location.. (Of course if
the size of your list changes, then you may need
to "delete" the data first, prior to copying it back into
your current worksheet.)

Clear as mud?




-----Original Message-----
Is there a way I can do a loop in an excel formula?
Is is possible to call a macro on a cell change event?
Is is possible to modify or add formulas to Excel?

When a formula is updated, I want a loop to fire off that
updates the value
of the cell by appending a number to it:
essentially I want to automatically change a column
without manually running
a macro. For example, I want the formula to make sure
the value in a given
column is unique, so I would want to change:
1
1
1
1
1

to:
1
1_1
1_2
1_3
1_4

Any help is appreciated...
thanks, dan


.

Loop within Excel Formula?
 

makes sense, I don't have the infinite loop problem because the cell that
fires off the event is not the cell that needs to be updated. What would a
method call look like to capture the event in VBA?

thanks, dan

wrote in message
...
You can run a macro/VBA code on a cell change event, but
you have to make sure that you don't get caught in an
infinite loop. Because everytime you change the cell to
look like you want, then you end up calling a cell change
event, to update the cell, that calls a cell change event,
to update the cell, etc.. etc.. etc.. So there has to be
some point of reference at the beginning of the function
that does the update to exit the update if the cell has
already been updated. For example, if you always know
that you will append _1 for the 2nd cell, _2 for the 3rd,
etc.. Then test the text to see if it's location
corresponds to the number added at the end. Ie. If Cell
1, 1 is changed
Cell 2, 1 is automatically updated with the data from Cell
1, 1 but with _1 added. So the test would be.. Does Cell
2, 1, have '_1', if so exit the update Or do the
comparison for the next cell, if not, update the cell.

Another thing you could do, would be to create all of the
updates in a separate worksheet... Then copy the updated
list to the current worksheet, and again you would still
need something to stop the cycle of updating, such as do
the target cells begin with the changed data.. If so, then
get out of the loop, if not, then make the list of data
and copy it to the appropriate location.. (Of course if
the size of your list changes, then you may need
to "delete" the data first, prior to copying it back into
your current worksheet.)

Clear as mud?




-----Original Message-----
Is there a way I can do a loop in an excel formula?
Is is possible to call a macro on a cell change event?
Is is possible to modify or add formulas to Excel?

When a formula is updated, I want a loop to fire off that
updates the value
of the cell by appending a number to it:
essentially I want to automatically change a column
without manually running
a macro. For example, I want the formula to make sure
the value in a given
column is unique, so I would want to change:
1
1
1
1
1

to:
1
1_1
1_2
1_3
1_4

Any help is appreciated...
thanks, dan


.

got it: Loop within Excel Formula?
 

ok, I figured it out. I was just unaware of the ability to use Private Sub
Worksheet_Change(ByVal Target As Range) within one of my sheet objects.
Then I can use Application.EnableEvents = False until I'm finished with my
work...
thanks for the help!
dan