negative value based on text in other cell

[trebor1956]

Hi, If I enter "Transfer to" in cell C1 then I want the numeric value that will be entered in cell E1 to be negative. As usual I have tried all sorts of stuff to no avail. Hope someone doesn't mind helping.

Rob

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Transfer to" in any cell in column C and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    If Target = "Transfer to" Then
        Target.Offset(, 2) = Target.Offset(, 2) * -1
    End If
End Sub

 

[James006]

@mumps Hello
seems to ... input in cell E will take place after input in cell C ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Column <> 5 Then Exit Sub
If LCase(Target.Offset(0, -2)) = "transfer to" Then
    Target.Value = Target.Value * -1
End If
End Sub

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 5 Then Exit Sub
    If Target.Offset(, -2) = "Transfer to" Then
        Target = Target * -1
    End If
End Sub

 

[trebor1956]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter "Transfer to" in any cell in column C and press the ENTER key.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Target.Column <> 3 Then Exit Sub
    If Target = "Transfer to" Then
        Target.Offset(, 2) = Target.Offset(, 2) * -1
    End If
End Sub

Hi,

Many thanks that works fine. Is there any way where I don't have to hit enter after typing in col C?

 

[James006]

How do you input your values in Column C ... ???

 

[mumps]

The code I posted in Post #2 assumes the number is already in column E before the value is entered in column C. The code I posted in Post #4 assumes the value is already entered in column C before the value is entered in column E.

 

[trebor1956]

How do you input your values in Column C ... ???

I choose from a drop down menu.
I have just again tried both and
If Target.Column <> 5 Then Exit Sub
your code with this seems to make the value negative very briefly before becoming positive
If Target.Column <> 3 Then Exit Sub
your code with this only works if the value is in col E already

 

[mumps]

your code with this seems to make the value negative very briefly before becoming positive

Are you using any formulae in those cells? It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach a screenshot (not a picture) of your sheet. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail, step by step, what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[trebor1956]

I'm sorry but I can't understand how to use XL2BB. Please see copied lines from s/sheet. I would enter text into the cols left to right and if col C contains "Transfer to" then when I enter figures into col E it automatically is a negative value. Hope this explains what I would like to do and sorry I'm not good enough to use the add in. There are no formulas as it stands.

TOTAL				4,686.31​
Transactions​				Comments	Pay ref
DATE	NAME	CATEGORY	REASON	££:PP
14-Jan-23​	From current a/c	FROM current	Transfer	500.00​	By RF	Transfer
18-Jan-23​	From current a/c	FROM current	Transfer	3,887.10​	By RF	Transfer
26-Jan-23​	From current a/c	FROM current	Transfer	2,298.00​	By RF	Transfer
01-Feb-23​	Interest added	Interest	Int added	1.21​		interest
22-Feb-23​	To current a/c	Transfer to	Transfer	-2,000.00​	By RF	Transfer