Excel =IF?

Closed
MitchW - Oct 28, 2010 at 08:56 AM
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
- Nov 1, 2010 at 09:59 AM
Im trying to put a =IF function in one cell to refer to another cell. After that i want it to post the results in yet another cell. If that makes sense. For example:

In A1: =IF(A2=1) Then i want A3 to have 10 added to it

I tried =IF(A2=1,THEN(A3=+10)) But this doesnt work.

Reason im doing this is because i have another base number in "A3" so i cant put the formula there. But i want to use whats currently in A3 and A2 to calculate a new A3.

Any suggestions?

5 replies

TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Oct 28, 2010 at 09:20 AM
Hi MitchW,

A formula won't do the job. A formula can't change the value of another cell.
A VB code can.

So try this code:
Sub test() 
If Range("A2").Value = 1 Then 
Range("A3").Value = Range("A3").Value + 10 
    End If 
End Sub 

Best regards,
Trowa
1
Ok well im still noob at this, lol sry.
But how do i get the cell to work as VB code?
0
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Oct 28, 2010 at 10:28 AM
No problem dude, we all started out as noobs.

There are different possibilities to run a code.
Let's take a look at the most common one.

To manually run the code implement the code as follows:
In excel hit Alt+F11 to open the VB editor.
Now goto the top menu's Insert > Module.
Copy/paste the code in the big empty white field.
The word behind Sub in the first line of code is the name of the code.
In this case it's test, but this can be changed to whatever one word you like.
After hitting Alt+F8 the name of the macro will be displayed and can be executed.
VB editor doesn't need to stay open for you to run the code.

Hopefully this will help you on your way.

Best regards,
Trowa
1
hackimist
Posts
37
Registration date
Thursday October 28, 2010
Status
Member
Last seen
December 28, 2013
129
Oct 28, 2010 at 09:29 AM
Nope i don't have any suggestions If my calculation is correct . . .

i don't think that's possible,trying to use =IF to refer something is possible but you also want it to post itself automatically in YET another cell?

I've never encounter a calculation like that. . . .

But i want to use whats currently in A3 and A2 to calculate a new A3?

It's like deleting a file that is in use.

But good luck maybe there's someone who knows about stuff
0
Yes the code does work. Now is it possible to have the program run just after i input a new number into A3?

Like the number was previously 10. running the script added 10 to a total of 20. now i want to change the original bast to 15 and rerun the script automatically.
0

Didn't find the answer you are looking for?

Ask a question
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Nov 1, 2010 at 09:59 AM
Hi MitchW,

What you are asking now can't be done imo.

You want the code to run when a change is made; no problem.
But think about what is happening:
You make change to A3,
this will call the code making a change to A3,
since a change has been made the code will run again making a change to A3, etc.

Let me show you some options.
When in VB editor, on the left side, above the inserted module, your sheets are visible, double click on the sheet which is in effect. You can also get here by right clicking on the sheets tab in excel and selecting the bottom option called "View code".
On top of the big white field you will see two dropdown boxes.
Click on the left one first and select worksheet.
Now when you click on the right one you will see a bunch of events which will make it possible to run your code automatically.
This might help you think of a solution.

Best regards,
Trowa
0