Formula Credit/Debit Toggle

irie267

New Member
Joined
Jul 1, 2012
Messages
25
Office Version
  1. 2016
Platform
  1. Windows
Hello All--

I am trying to create a macro that basically takes a formula (or hardcoded value), wraps it in parenthesis with a negative sign in front, and then removes the negative sign and parenthesis. The process I am thinking of is below:

1. [Formula or Hardcoded Value]
2. Macro activated: (-[Formula or Hardcoded Value])
3. Macro activated again (back to original cell information in step 1): [Formula or Hardcoded Value]

I have tried to figure this out, and am able to get to step #2 above, but cannot figure out how to revert it back to the original value (step #3). Any help would be appreciated.
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi @irie267. Thanks for posting on the forum.

I'm not sure what you need or what you need it for.
But let's see if the following works for you.

Scenario 1. Assuming that in cell B2 you have a formula
Step1:
1683920190054.png
Step 2:
1683920324823.png
Step 3:
1683920440677.png

Scenario 2. Assuming that in cell B2 you have a value.
Step1:
1683920544984.png
Step 2:
1683920577017.png
Step 3:
1683920604803.png

If all of the above is correct, then try the following:

VBA Code:
Sub formulaDC()
  Dim c As Range
  Dim ant As String
 
  Set c = Range("B2")
  If c.HasFormula Then
    ant = c.Formula               'step 1
    c.Value = "(-" & ant & ")"    'step 2
    c.Formula = ant               'step 3
  Else
    ant = c.Value                 'step 1
    c.Value = "(-" & ant & ")"    'step 2
    c.Value = ant                 'step 3
  End If
End Sub

If it is not correct, then you should give examples of what you have and what you expect as a result.
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
 
Upvote 0
Hi @irie267. Thanks for posting on the forum.

I'm not sure what you need or what you need it for.
But let's see if the following works for you.

Scenario 1. Assuming that in cell B2 you have a formula
Step1:
Step 2:
Step 3:

Scenario 2. Assuming that in cell B2 you have a value.
Step1:
Step 2:
Step 3:

If all of the above is correct, then try the following:

VBA Code:
Sub formulaDC()
  Dim c As Range
  Dim ant As String
 
  Set c = Range("B2")
  If c.HasFormula Then
    ant = c.Formula               'step 1
    c.Value = "(-" & ant & ")"    'step 2
    c.Formula = ant               'step 3
  Else
    ant = c.Value                 'step 1
    c.Value = "(-" & ant & ")"    'step 2
    c.Value = ant                 'step 3
  End If
End Sub

If it is not correct, then you should give examples of what you have and what you expect as a result.
--------------
I hope to hear from you soon.
Respectfully
Dante Amor
--------------
Thank you for your response--it is almost what I am looking for. The issue lies in Step #2 both scenario 1 and 2. I essentially just want to wrap the formula or hard coded value in paranthesis with a negative sign in the front to turn the number into a negative quickly, but then also be able to revert it back to positive very easily. I've adjusted your example to what I am looking for below. Thanks again for the help.

Scenario 1. Assuming that in cell B2 you have a formula
Step1:
1683922725904.png
Step 2:
1683922782428.png
Step 3:
1683922807078.png

Scenario 2. Assuming that in cell B2 you have a value.
Step1:
1683922854092.png
Step 2:
1683922872058.png
Step 3:
1683922854092.png
 

Attachments

  • 1683922690086.png
    1683922690086.png
    5.8 KB · Views: 2
Upvote 0
Hi @irie267.
Thanks for the examples. In this world, I must be very literal, and I must deliver what you ask for

If you ask for the minus sign inside the parentheses, then I must do it:

1683928246596.png

----- --
Now the minus sign is outside the parentheses.

1683928695543.png


It is necessary that you give examples, that way it will be easier to give you a complete solution.

Try the macro with the changes:
VBA Code:
Sub formulaDC()
  Dim c As Range
  Dim ant As String
  
  Set c = Range("B2")
  If c.HasFormula Then
    ant = c.Formula                                    'step 1
    c.Formula = "=-(" & Replace(ant, "=", "") & ")"    'step 2
    c.Formula = ant                                    'step 3
  Else
    ant = c.Value                 'step 1
    c.Value = ant * -1            'step 2
    c.Value = ant                 'step 3
  End If
End Sub

However, the example is for cell B2, I really don't know if it's for one cell or multiple cells, but I hope it helps you what you really need.

Cordially
Dante Amor
----- --
 
Upvote 0
Hi @irie267.
Thanks for the examples. In this world, I must be very literal, and I must deliver what you ask for

If you ask for the minus sign inside the parentheses, then I must do it:


----- --
Now the minus sign is outside the parentheses.



It is necessary that you give examples, that way it will be easier to give you a complete solution.

Try the macro with the changes:
VBA Code:
Sub formulaDC()
  Dim c As Range
  Dim ant As String
 
  Set c = Range("B2")
  If c.HasFormula Then
    ant = c.Formula                                    'step 1
    c.Formula = "=-(" & Replace(ant, "=", "") & ")"    'step 2
    c.Formula = ant                                    'step 3
  Else
    ant = c.Value                 'step 1
    c.Value = ant * -1            'step 2
    c.Value = ant                 'step 3
  End If
End Sub

However, the example is for cell B2, I really don't know if it's for one cell or multiple cells, but I hope it helps you what you really need.

Cordially
Dante Amor
----- --
My apologies--you are correct. In my attempt to be as specific as possible, I made an error in explaining exactly what I am looking for. That said, this macro is not working as I envisioned for two reasons. In my mind it should be able to be applied to a selection of cells not just B2. Additionally, it is not working in cell B2--nothing happens when the macro is activated. I have a formula in the cell, but I am not sure I am doing everything right. Really appreciate the work.
 
Upvote 0
In my attempt to be as specific as possible
The macro doesn't work for multiple cells simply because you didn't mention it anywhere.

What I am giving is an example so that you can adapt it to your needs.

Then you will have to be more specific with your requirement.

The macro works correctly, it does step 1, then step 2 (sets the sign) and then step 3 (returns the previous value), but it does it too fast that you can't see it.
If you test cell B2 and run the stepping macro you will see that it changes to -20 and then back to 20.

But you don't comment what the final goal is, I don't know what you need and how I don't know what you need because it's impossible for my macro to do it. I was only responding to your original request but without further information my hands are tied.

Although I would like to continue with my help, due to lack of information, I cannot continue.
 
Upvote 0
The macro doesn't work for multiple cells simply because you didn't mention it anywhere.

What I am giving is an example so that you can adapt it to your needs.

Then you will have to be more specific with your requirement.

The macro works correctly, it does step 1, then step 2 (sets the sign) and then step 3 (returns the previous value), but it does it too fast that you can't see it.
If you test cell B2 and run the stepping macro you will see that it changes to -20 and then back to 20.

But you don't comment what the final goal is, I don't know what you need and how I don't know what you need because it's impossible for my macro to do it. I was only responding to your original request but without further information my hands are tied.

Although I would like to continue with my help, due to lack of information, I cannot continue.
My apologies for not being specific enough--as you can imagine articulating the end goal is a little difficult. I now realize that the macro is working, but it is too fast to realize unless I am paying very close attention. The goal is to run the macro on =[starting formula or hardcode], have the parenthesis and negative sign show up =-([formula or hardcode]), run the macro again and get =[formula or hardcode], etc. I apologize for the lack of clear communication, I am trying.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top