simple IF function

[ridgleye]

good evening all - looking for a little help here. I've had a long day and my brain is mush. I just can't get my brain to really function on any level right now.


Worksheet has four cells:

B1 - date

B2 - <data validation> two choices (Depoist/Withdrawl)

B3 - <data validation> 13 choices

B4 - [invoice amount]


All I want to do is is have B4 become a negative number when the user selects "Withdrawl" in B2. The trick for me right now is that B4 is an open cell in which the user will need to put an invoice amount into.


Once the user hits the submit button for the Macro the data will feed into a database where I want to be able to add/subtract as necessary (based on deposits or withdrawls).


Any help you can provide would be appreciated.

 

[NARAYANK991]

Hi ,


Is this what you are looking for ?


Private Sub Worksheet_Change(ByVal Target As Range)

If Range("B2").Text = "Withdrawal" Then

Range("B4").Value = -Abs(Range("B4").Value)

End If

End Sub


Narayan

 

[ridgleye]

NARA... Thank you for the post. I don't know VBA yet so don't know how to incorporate that into the Code area for my worksheet. Does it matter what the name of my worksheet is (in reference to your specific code)? IE - my worksheet is named "MacroForm".


Sorry for being such a rook

 

[prasaddn]

you may also try if function with one helper cell/columns.


if you need the invoice amount in B4 only, then make user to enter the amount in B5, and apply the formula mentioned below in B4 cell.


=IF(B2="Withdrawl",-ABS(B5),B5)


Regards,

Prasad DN

 

[Luke M]

Narayank991's code is not specific to a sheet name. =)


To install, right click on sheet tab, view code, paste in new code. Close the VBE.

 

[NARAYANK991]

Hi ,


The macro is for a Worksheet Change event ; it is triggered whenever there is any kind of change in the worksheet ; one way is by entering data into any cell in the worksheet.


So , whichever worksheet tab contains your data entry into cells B2 and B4 , right click on that sheet tab , and select View Code ; paste this code into the window which opens. That's all.


If you say your worksheet is "MacroForm" , select that worksheet tab , and do the above.


Whenever you enter new data into cell B2 or B4 , this code will be executed , and you should see that even if you enter a positive value into cell B4 , if B2 contains the text "Withdrawal" , the positive amount that you entered , will be replaced by a negative amount.


Narayan

 

[ridgleye]

Thank you all for the help.


As a FYI - using the [-abs] code gives an error as it's a circular reference. I played around with it and got it to work but the first time I copy/pasted it I ended up having to kill the computer as it wouldn't allow me out of the VBA box. It was crazy.


I'm enjoying learning how this all works and ties in together. Thank you!

 

[Luke M]

Doh! Code fix:

[pre]

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B2").Text = "Withdrawal" Then
'THIS IS IMPORTANT!!!
Application.EnableEvents = False

Range("B4").Value = -Abs(Range("B4").Value)
'Make sure you turn it back on
Application.EnableEvents = True

End If
End Sub

[/pre]

 

[NARAYANK991]

Hi Luke ,


Thanks for the correction.


Narayan