OK I left the Comboxs the way I described below
Please follow the steps
1. In Row 7 column "A" enter "TCO" later you can change things if you want.
2.Be sure and enter all you names into row 7 starting in Column "C" and fill across as far as you want.
I have a script here for you.
You will need two Activex Comboboxes
And two Activex Command Buttons
One of the Command Buttons will be to load your names from Row(7) into the Comboboxes
The other Command Button will be used to run your script
To make things easier for you I want you to name your two Comboboxes. "Into" and "Out of"
The Textbox where you enter your Amout should be named "Textbox1"
To name your Comboboxes right click on the Combobox and choose properties and change the name.
Click on the Command button once to load the names into the two Comboboxes.
This only ever needs to be done once unless you add more names to Row(7)
Then enter your amount into the textbox named "Textbox1"
Choose the name you want from the two comboboxes and then press the command button
You can add more names in Row(7) if you want.
Here are the scripts:
This script goes into a command button does not matter the name.
When you activate this button it loads all your names into both Comboboxes
Code:
Private Sub CommandButton1_Click()
'Install this script in Command button to load you Comboboxes with values
Dim i As Long
Lastcolumn = Cells(7, Columns.Count).End(xlToLeft).Column
Into.Clear
OutOf.Clear
For i = 3 To Lastcolumn
Into.AddItem Cells(7, i).Value
OutOf.AddItem Cells(7, i).Value
Next
End Sub
This script goes into a command button does not matter the name.
When you activate this button it runs your script. Be sure and select a value from each Combobox before activating this button;
Code:
Private Sub CommandButton2_Click()
'Install script in Command Button to perform you script
Dim c As Range
Dim Lastrow As Long
Lastcolumn = Cells(7, Columns.Count).End(xlToLeft).Column
For Each c In Range(Cells(7, 3), Cells(7, Lastcolumn))
If c.Value = Into.Value Then
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(Lastrow, c.Column).Value = TextBox1.Value
End If
Next
For Each c In Range(Cells(7, 3), Cells(7, Lastcolumn))
If c.Value = OutOf.Value Then
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Cells(Lastrow, 1).Value = "TCO " & Lastrow - 7
Cells(Lastrow, c.Column).Value = "-" & TextBox1.Value
End If
Next
End Sub
See how this works and let me know please.