Macros that should automatically run when a cell value change ++

Hi,

I am quite new to VBA programming in excel, but I try to learn something new each day.

So in general, I have created an excel sheet that basically solves an equation with the goalseek function. I did this when changing a value, so I used this code:
_______________

Please Login or Register  to view this content.

________

So, this equation is solved automatically when I change a variable, which I can do from a cell in another worksheet. I then use the command ='Sheetname!'C6 <-- or something in that cell.

So, there are some few things that I would love to know, and I spent 6 hours last sunday figuring out how I can not do them

One of the things I would love to do is the following:
I would like to be able to change the value of the cell (which makes the program run) from several other cells in a different worksheet. At the same time, I want excel to automatically copy several values from the same worksheet (the equation worksheet) and paste them in another worksheet each time the cell changes value.

E.G:
I have a column with different values from dropdown lists. The cells in the row needs to be limited to, let's say 20 cells that can affect the value of the cell that runs the code that solve the equation.
Every time the code solves an equation, I would like to copy 6-7 values from the sheet that has the equation on it to the sheet that I changed the cell value (the active row in the sheet which I changed a cell in a drop down menu (this is the same sheet that has the cell column of 20 cells with drop down menus)).

I the program to be able to do this for all the 20 cells. But I have no Idea how.

Also, If I really want to learn VBA in excel, where should I start?


If you see this thread for the first time just go to the bottom where I attached my excel sheet

It looks like what you need is a Worksheet Change Event.

Please Login or Register  to view this content.

The code evaluates to if the changed cell (target) is within the range, the intersection expression is false and the program continues. If the target is outside the range then the intersection is nothing and the program exits without executing any more code.

Thank you for that!

Just a quick question, should I use this code in the sheet where I want my values transfered, or should I use it in the sheet where I have my equation (the equation sheet)?

Also, is it possible to have several of these "autorun" codes in one sheet?

I also want to know how you get these values in the column transfered to one cell in another sheet when it is active?

Use this code on the sheet where the values are changed.

You can have multiple "live' triggers for the code. By "live" I mean if you have Private Sub Worksheet_Change(ByVal Target As Range) the code is trying to run any time ANY cell on the sheet is changed.

That's why the sample the If Intersect line. it jumps out of the code if what's being changed is not in the range specified.

So the answer is, yes you can have multiple live triggers, but you have to figure out how to tell the code when to execute them. For example if you just want to look at four cells. Let's say B1, B3, B5 and B7, you can write code that looks like:

Please Login or Register  to view this content.

This is only one such scheme. You could also use logic such as

Please Login or Register  to view this content.

Without seeing the specifics of your worksheet, this is the best I can offer.

Thank you again for reply!

I really hate to bother you so much, but I really appreciate your help.
I have now entered the following code to remove one value from one sheet to another one.

Please Login or Register  to view this content.

So, when I run this code on any other worksheet than the one named "Calculations", it works. When trying to run it in the "Calculations" worksheet I get the following:

Run-time error '9':
Subscript out of range

What is worth mentioning is that the worksheet I am trying to copy the values into has the following code in it:

Please Login or Register  to view this content.

Since this is the only worksheet I am not able to paste any values into, I am starting to believe that the code above is ruining everything. I still need the code to make the calculations though. Any ideas on what to do?

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hi,

I have now attached an excel sheet that demonstrates my problem.

Basically I want to change the values in the "D" column in sheet 2 (Ark 2) in Norwegian, the values should transfer to C4 in sheet 1 (Ark1) and then the goalseek function should run automatically.

The sheet should be attached.

This is not the sheet that I am working on, but the exact same problem. The sheet I am working on is too big and too complex to start looking into (not complex in vba programming, but everything else).

Thank you for any assistance!


If you are able to solve this one, please also show me how to transfer values from sheet 1 (Ark1) to sheet 2 (Ark2) automatically after the equation is solved and paste them to the right of the row where you changed the value (in sheet 2 (Ark2)).

I'm a bit confused as to what your remaining question is.

On Ark2 you mention you want to copy the range D2 to D8 to cell C4 on Ark1 when Cell C4 on Ark1 is changed. Is this correct?

I'll have to read up on goal seek. I generally use solver.