Cell updated when if condition is changed (macro)

Closed
Jerry - Jun 5, 2016 at 05:00 PM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 16, 2016 at 10:47 AM
Hello,

Can anyone help me with a simple thing I cannot get done ?

Using a macro, I want the value (integer) of a cell to be changed depending on the value (string) of another cell with the if statement.

I can do it at the beginning, but if I change the string, the integer doesn't update in the other cell.

Thanks for your help

2 responses

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 13, 2016 at 11:37 AM
Hi Jerry,

If the string doesn't match the IF statement then nothing will happen.

Could you give more details for better understanding?

Best regards,
Trowa
Thanks for the reply!

Actually, I was able to find a solution to my issue, with Private sub Worksheet_Change(ByVal Target As Excel.Range) mixed with If Not Intersect(Target, Range(...)) and calling my macro in the If Not function. This way, if my string changes, the mathematical operation (written in my macro) is changed as well. To be more accurate, I'm creating a budget and need to categorize payments annually or monthly (choice made with validation data).

But that brings up other issues.

First, it would be more efficient if I could putt my macro in the if function of a cell (and not a macro). I would simply type in a cell =if(condition, my macro, something else) and my macro would run and use the ActiveCell and offsets to do the magical operations. Is it possible???

That would be more efficient in the case I add another row or column for instance, because right now, the range of my target is constant and is no longer the right one if I add a column.

Secondly, with macros, is it possible to create a variable that equals the content of a cell, but even if the cell moves with its content, the variable remains the same and the macro still knows the value of that variable? In other words, I don't want to create a variable based on something like RC[3].

Thank you very much

Jerry
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Jun 16, 2016 at 10:47 AM
Hi Jerry,

For you first question:
When you put the formula: =if(condition, TRUE, something else) in A1 (for example), you can then let the macro run when the value in A1 is TRUE.

For your second question:
You can put a variable in Excels memory by using:
Dim mValue (or any other word not used by Excel) as string (for text) or double/integer/long (for numbers depending on the size)
mValue = Range("A1")
Or is that exactly what you don't want?
When you know the variable will always be below a certain header then you can find that header and use offset to get to your variable.


Hopefully that helped a little, but I will probably be able to help you better if I know what you are trying to achieve with the setup you have.

Best regards,
Trowa