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
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - Jun 16, 2016 at 10:47 AM
Related:
- Cell updated when if condition is changed (macro)
- Count if cell contains number - Excel Forum
- Whatsapp date changed to 1970 - Guide
- If cell contains date then return value ✓ - Office Software Forum
- Spell number in excel without macro - Guide
- If cell contains specific text then return value in another cell ✓ - Excel Forum
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
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
If the string doesn't match the IF statement then nothing will happen.
Could you give more details for better understanding?
Best regards,
Trowa
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Jun 16, 2016 at 10:47 AM
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
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
Jun 15, 2016 at 12:34 PM
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