Enter a number in two Cells simultaneously, One as +ve & one as -ve

vikrampnz

Board Regular
Joined
Jun 20, 2006
Messages
111
Office Version
  1. 2007
Platform
  1. Windows
I have a spreadsheet (“Account”) with columns assigned to each person e.g. Rick, Tom, Angela, Sue . The names are mentioned in the top row from C1 to F1 as headers. I am trying to have two comboboxes to select “TO” and “FROM” names from the same list showing above names and the amount that needs to be transferred from one person to another.

For example, From: “TOM” , to: “Angela” , Amount: 100 (To be entered in a text box) and the row should show “-100” in the last emply cell in column TOM and “100” in the last emply row in column ANGELA.

I am trying to find a VB code to do this. Can someone help! Your help would be much appreciated!

Thanks
 
Last edited:

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
I'm going to give you a point in the right direction. Look for "finding last value in a column," or "find last used row." Hint: it involves Range(xyz).End(xlUp).Offset(1,0). This will give you the empty row you are looking for. Then just enter the value and the negative vaule in the appropriate columns. Something like:
Code:
TransferAmount = InputBox(blah....)
LastRow = Range...
wks.Cells(LastRow, FromCol) = TransferAmount
wks.Cells(LastRow, ToCol) = -TransferAmount
Have fun!!
 
Upvote 0
Oh - you want to be able to get the columns I assume. You could do something like this:

Code:
FromCol = wks.Range("C1:F1").Find(cboFrom.Text, , , xlWhole).Column
ToCol = wks.Range("C1:F1").Find(cboTo.Text, , , xlWhole).Column
Where cboTo and cboFrom represent the To and From combo boxes, respectively. Make sense?
 
Upvote 0
Thanks for your response Russell !

Sorry I am confused, as I am a novice in this.

I cant seem to be able to attach my test Excel sheet to this post.

I am trying to achieve following:

My Spreadsheet has 15 columns with individual people's names as Header for each column. (They go from B7, C7,D7,E7,F7.....till P7, which is effectively my header row)
Column A has the Transaction Code for each transaction added i.e. CT001, CT002 etc

I have to keep transferring credits from one person to another. for example, If TIM transfers 100 Credits to Angela, then I enter a "-100" in TIM's column and a "100" in Angela's column. I pick the last empty row in column A (which is the transaction ID column) and enter this manually in that row under respective people's columns.

I am trying to automate this process by adding 2 x Comboboxes viz. "cboFrom" & "cboTo", a Textbox for entering transfer amount viz. "TRAmount" and a command button named "cmdEnter"

Currently I am facing following issues:

1. My Combobox does not seem to pick the Name List range directly from my horizontal header row ..(B7:P7)
2. I tried to run the code you have suggested, but its giving errors. Due to my lack of experience in this, I am unable to figure out the problem in it.

I really appreciate your help!

Thanks
 
Upvote 0
I was writing a script for you but now I'm confused. You said in your original post:

The names are mentioned in the top row from C1 to F1 as headers.

But now in post #4 you said:

(They go from B7, C7,D7,E7,F7.....till P7, which is effectively my header row)

So are the names in Row(1) or Row (7) ?

This can cause us problems when you change what you want.
 
Upvote 0
Hello

The names are in row 7 now, as I changed the sheet to accomodate the combobxes at top

thanks
 
Upvote 0
Well this makes things more complicated because now we have to tell the script to look in row (7) for the names.
Then the first time the script runs we have to tell the script to enter you amounts into row(8) and next time into Row (9) and on and on.

We would normally tell scripts to enter values into the row below the last row with data but now we cannot do that because you want rows 1:7 to be empty

And I'm not sure what this has to do with what we are doing.
Column A has the Transaction Code for each transaction added i.e. CT001, CT002 etc

You never mentioned this in your original post:
 
Upvote 0
You are right. Really sorry for the confusion.

Thanks for your help!
 
Upvote 0
Hi....I can place the comboboxes to the right side of the table and push the table up again, so that the header row will be in row 1 again...if that helps the situation.
thanks
 
Upvote 0
I have solved the problem. We can go with row (7)

Tell me more about this:

Column A has the Transaction Code for each transaction added i.e. CT001, CT002 etc

And tell me do you know how to name your comboxes?

If not tell me the names of your Comboboxes

I have named mine "Into" and "Out of"
And the name of you Textbox where you will enter the Amount
I have mine names Textbox1

And all these controls need to be ActiveX controls.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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