Macro to modify chart's source data and populate a cell 1

[ShikkurDude]

=IF((B88<>""), (A87+7),"")

I have a worksheet with two columns - A and B. Beginning in row #2, I would like a macro that would do the following two things:

- when a value is entered in column B, the macro should set the value of the same row's column A to 7 more than the previous column A's value.

- the source data for Chart1 should be "grown" at the same time.

In other words, if Chart1's source data was from cells A2-A10 and B2-B10, and now the user just added a value into B11 - the macro should (1) set A11 to A10 + 7, and (2) it should make the source data for Chart1 to A2-A11 and B2-B11.

I have no idea how to do this and would really appreciate some help...

Thanks so much,
E.

 

[ShikkurDude]

Please ignore that little formula at the top of my previous post... Oops...

 

[xlbo]

This can be done without VBA. Firstly, you wiull need formulae in col A similar to the one you inadvertantly posted at the top of your 1st post

Secondly, you need to create a DYNAMIC NAMED RANGE for each of your chart series - the following FAQ from the MSOffice forum shows you how to do this...faq68-1331

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ShikkurDude]

Geoff,

OK, the formula I got (as you can see) - but I have to manually fill the formula in for all cells. The dynamic named range looks complicated - isn't there an easy way to do that VBA?

E.

 

[xlbo]

Dynamic named ranges are easy - certainly easier than renaming the range in VBA...but given that you need to enter the formula as well, you will need to look at EVENTS - specifically, the worksheet CHANGE event.

To get the code to name a range, try using the macro recorder to record yourself naming a range.
To get the code to find the last row of data, have a look at the FAQs area - there are at least 2 in there
To look at how you enter formulae via code, again, try the macro recorder.

You will need to wrap the above in the worksheet_CHANGE event, testing for the change happeingin in a specified column by using the TARGET keyword - within the CHANGE event, TARGET refers to the cell that has been changed and is a range ovject so has all the standard range properties (.column, .row, .value etc etc)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ShikkurDude]

Wow, I'll look that over. Thanks, Geoff!!!
E.

 

[xlbo]

No probs - have a go and see what you can get working. Any problems, post the code that you've tried with an explanation of what you wanted it to do and what it actually did and I'm sure I or someone else will be able to help...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question