Need help with macro which would change > to <

[yessuz]

Hi.
Background:

I have a small table, in which I have 4 lines ar 12 columns.

On the last line there's a formula which checks first 3 lines:

Please Login or Register  to view this content.

Basically what it does, it checks if:

E7 (Actual) is more than E6 (Plan), and P8 (Forecast) is more than P6 (Plan) - it means that target exceeded.
E7 (Actual) is less than E6 (Plan), and P8 (Forecast) is less than P6 (Plan) - it means that Below Target.
E7 (Actual) is less than E6 (Plan), but P8 (Forecast) is more than P6 (Plan) - it means that there is a risk for year end result.
E7 (Actual) is more than E6 (Plan), and P8 (Forecast) is less than P6 (Plan) - it means that there is a risk for year end result.

Row 9 then displays the correct message, let's say "Target Exceeded"

File attached: book.xlsx

Problem

However, this works only when I need the end result to be more than Plan (More is better). if I need to check for result where Less is better, I need to change all the ">" and "<" places, that it would check if actual is less than target and would say that it's fine.


Possible solution?
in any way, I do not know what kind of metrics might be used, so I want to create two small buttons with macros, which would change the text in formula. This button should rewrite the formula for each cell in the row 9 (total - 12 cells from column E to column P). Also, there can be another macro (i could modify the first one) which reverts all changes back to normal (initial, More is better)

It also could be some kind of universal macro, as these buttons should be near each of 20 tables in the same spread sheet, as I do not know which metrics which department would use.

Could anyone guide me on the right direction with a suggestion for the macro and how to implement it in the right way?

thank you!!!

[yessuz]

Bump.
guys, please guide me

[IGEDIT]

Hi yessus,
Look at the modified book.xls. It's only a quick and rough method and may not be what you want but give it a try.
It may need some alterations which I am sure you can manage.

Modified book2.xlsm

[IGEDIT]

yessus,
Scrap the last modified book2! I have re-done the code and made it easier to follow.

[IGEDIT]

Bump
How are you doing? Need any more help?

[yessuz]

hi.
thanks for the help.
I was off my computer for the evening..

thank you for the file. I'm checking it at the moment. it looks like ok

the only question is: if I have 20 such small tables and need to have the buttons for each of tables, in that case I think I need to have 40 macros - 2 macros per table.

correct me if I'm wrong.

[IGEDIT]

Hi yessus,
If the last code works and if it will be the same for all your small tables you only need to point it in the right direction. Will these tables be on the same worksheet, or will they be on separate worksheets?
Give me as much info. as possible and I will try and help.

[yessuz]

Hi.
it would be on the same spreadsheet - people would have to enter data related to their department. It would look like this:
http://i.imgur.com/dXnz4a9.png

I think that I can make it with separate codes of each table, however, maybe there is more efficient way to do it?
on the other hand, I do not know what kind of metrics (20 of 600 available) dept. will use, so I think manual selection for each table would work fine.

[IGEDIT]

Hi again,
Depending on whether or not you are comfortable using the Developer tab in Excel there is a way that I can go forward with this thread. I am far more familiar with VB6 than the M.S. Office Object but where there is a will there is normally a way! If you wish to go with the method I have outlined in the last example, I can show you how to continue, but it depends on your answer to my first question. Hope you follow all that!

[yessuz]

Hi.
I'm kind of beginner in VBA, however in past I have modified some macros I found on web in the way I needed, so developer tab and vba console is not a new thing for me.. but I was kind of surprised when I saw that you put code on the button, as I used to write simple macro in visual basic console and then created the button which had that speciffic macro assigned.
but still, i think that I could edit the code for the button by myself... maybe some questions might arise during that.

but if you have any other ideas for the most efficient way, well - tell it

[IGEDIT]

Hi,
A macro is just the same as Vba Sub. I have attached ANOTHER workbook for you to peruse. This time there is only one button per table but it is a dual purpose button(i.e. it performs a task depending on the button caption.)
Give it a try, it's only a sample and will need transplanting onto your own spreadsheet. Please ask whatever you want.
Put some credible data into it and see what happens.

[yessuz]

ok, thanks! I'll check it on monday and will let you know!
thanks again!

[IGEDIT]

Hi yessuz,
Have you thought about inserting another ROW beneath the one containing your formula and adding your "Less Than" formula into this ROW? You could put "More Than" alongside your initial ROW and "Less Than" alongside the new ROW to indicate to users what was being shown. This would seem to be easier than your first idea of using buttons.

[yessuz]

well, this is bad idea, as the contents of each such row later is displayed in another sheet. Since I do not know which metrics will be used i think I would face some problems in another sheet )

[yessuz]

Hi. i have edited the macros in accordance to my needs.
here's your code:

Please Login or Register  to view this content.

Now, when I push the button I have the Run-time error "6": Overflow

I use two buttons, (using modified book 3).

After the error message i see that old data was deleted, but new - missed. what's the issue?!

[yessuz]

Amm, I found the problem:

had to change this:

Please Login or Register  to view this content.

the only issue is, that there is huge lag - like 20 seconds, from the push of the button to the change of the result

what could be the problem?!

[IGEDIT]

Hi again,
Please forgive me for not debugging the last effort. Have done it again so please try it out.

[yessuz]

That's cool, you're helping me a lot!!!

well, there's last issue which I forgot, and now I found that it's kind of wrong in the code you are doing

It becomes more and more complex, but that's not the issue.
If you would look at my initial post, what I wanted, was actually, to change values in the formula. This is required as the macro button should be pushed just once, during the initial data input. Later, only data for "actual" and "Forecast" would be edited/added. In this case, the formula in the row would look at the data in cells, is actual more then budget or less and would change the output accordingly.

What your macros do, is rewriting of the contents of cells. But it does a full rewrite of cell contents, it does not the change the formula contents.

any solutions?

in general, what I actually need is just push the button and have this:

Please Login or Register  to view this content.

changed to this:

Please Login or Register  to view this content.

for the range of cells.

and I prefer two buttons...

thanks!!!

[yessuz]

Oh, sorry again.

I've figured out what I actually need and heres my code:

Please Login or Register  to view this content.

Not sure, is it possible to speed it up?

[IGEDIT]

yessus, Hi,
If your real target is to have only 2 buttons to cover ALL the tables on your sheet then you need some method of identifying which table to apply your code to. e.g. the metrix(whatever that is?) number: KP14/KP15/KP16 etc.

Is this what you are after? I am beginning to struggle to understand exactly what need to achieve.
In your formula in all it’s variations you are assessing the differences between various cells and then
assigning different values for the same conditions.

Please Login or Register  to view this content.

As the values of the cells of the cells are static, it does not make sense
to me how this can be possible.

[yessuz]

hi.
No, my task is to have two separate buttons for each table. sorry for miscommunication from my side.
i think I got it.
thanks again anyway!

[IGEDIT]

Hi,
Try a final one!

[IGEDIT]

BUMP
Hi yessuz,
How are you getting on with your project?
I would be interested to know if you have solved it yet.
I have managed to construct an answer using just 2 buttons for a complete sheet of tables!
regards.