Select Case
 

I am trying to us a Select Case Construction but I am not able to get the
program to choose the branch.

I have a spreadsheet similar to the following:
A B C D
E
1 Old
2 Symbol Shares TotalShares
3 AMD 50
4
5 - New Trade -
6 Trade Type Symbol New Shares
7 ADD AMD 25
8

I want my code to put a value in cell below "TotalShares" (D3) based on the
entry in the cell below Trade Type (A7).

If the Trade Type is:
"ADD", add New Shares to Old Shares.
"SELLPARTIAL", subtract New Shares from Old Shares
"SELLALL", subtract New Shares from Old Shares and enter "sold" in E3

Here is my code.
My problem is that I want it to execute the statements below Case Add but it
skips over that code. I don't know how to indicate that this is Case ADD.

How to I fix this? Thanks, Alan


Sub ProcessTrades()

Dim TrType As Range
Dim OldShares As Range
Dim TradeShares As Range
Dim TotalShares As Range

Set OldShares = Range("C3")
Set TradeShares = Range("C7")
Set TotalShares = Range("D3")
Set TrType = Range("A7")

Select Case TrType

Case Add
TotalShares.Value = OldShares.Value + TradeShares.Value

Case SellPartial
TotalShares.Value = OldShares.Value - TradeShares.Value

Case SellAll
TotalShares.Value = OldShares.Value - TradeShares.Value
Range(E3).Value = "sold"

End Select
End Sub


achidsey

Select Case
 

try this automatic idea which is accomplished by what you type in col a.
Modify to suit.

right click sheet tabview codeinsert thismodify to suitsave

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 1 Then Exit Sub
Select Case Target
Case "a": Target.Offset(0, 1) = Target.Offset(0, 2) - Target.Offset(0, 3)
case "buy"
case "sell"
End Select
Cancel = True
End Sub

--
Don Guillett
SalesAid Software

"achidsey" (notmorespam) wrote in message
...
I am trying to us a Select Case Construction but I am not able to get the
program to choose the branch.

I have a spreadsheet similar to the following:
A B C D
E
1 Old
2 Symbol Shares TotalShares
3 AMD 50
4
5 - New Trade -
6 Trade Type Symbol New Shares
7 ADD AMD 25
8

I want my code to put a value in cell below "TotalShares" (D3) based on
the
entry in the cell below Trade Type (A7).

If the Trade Type is:
"ADD", add New Shares to Old Shares.
"SELLPARTIAL", subtract New Shares from Old Shares
"SELLALL", subtract New Shares from Old Shares and enter "sold" in
E3

Here is my code.
My problem is that I want it to execute the statements below Case Add but
it
skips over that code. I don't know how to indicate that this is Case ADD.

How to I fix this? Thanks, Alan


Sub ProcessTrades()

Dim TrType As Range
Dim OldShares As Range
Dim TradeShares As Range
Dim TotalShares As Range

Set OldShares = Range("C3")
Set TradeShares = Range("C7")
Set TotalShares = Range("D3")
Set TrType = Range("A7")

Select Case TrType

Case Add
TotalShares.Value = OldShares.Value + TradeShares.Value

Case SellPartial
TotalShares.Value = OldShares.Value - TradeShares.Value

Case SellAll
TotalShares.Value = OldShares.Value - TradeShares.Value
Range(E3).Value = "sold"

End Select
End Sub


achidsey

Select Case
 

Try
case "Add" etc

Wolf

"achidsey" wrote:

I am trying to us a Select Case Construction but I am not able to get the
program to choose the branch.

I have a spreadsheet similar to the following:
A B C D
E
1 Old
2 Symbol Shares TotalShares
3 AMD 50
4
5 - New Trade -
6 Trade Type Symbol New Shares
7 ADD AMD 25
8

I want my code to put a value in cell below "TotalShares" (D3) based on the
entry in the cell below Trade Type (A7).

If the Trade Type is:
"ADD", add New Shares to Old Shares.
"SELLPARTIAL", subtract New Shares from Old Shares
"SELLALL", subtract New Shares from Old Shares and enter "sold" in E3

Here is my code.
My problem is that I want it to execute the statements below Case Add but it
skips over that code. I don't know how to indicate that this is Case ADD.

How to I fix this? Thanks, Alan


Sub ProcessTrades()

Dim TrType As Range
Dim OldShares As Range
Dim TradeShares As Range
Dim TotalShares As Range

Set OldShares = Range("C3")
Set TradeShares = Range("C7")
Set TotalShares = Range("D3")
Set TrType = Range("A7")

Select Case TrType

Case Add
TotalShares.Value = OldShares.Value + TradeShares.Value

Case SellPartial
TotalShares.Value = OldShares.Value - TradeShares.Value

Case SellAll
TotalShares.Value = OldShares.Value - TradeShares.Value
Range(E3).Value = "sold"

End Select
End Sub


achidsey

Select Case
 

Thank you. That worked.
--
achidsey


"Wolf" wrote:

Try
case "Add" etc

Wolf

"achidsey" wrote:

I am trying to us a Select Case Construction but I am not able to get the
program to choose the branch.

I have a spreadsheet similar to the following:
A B C D
E
1 Old
2 Symbol Shares TotalShares
3 AMD 50
4
5 - New Trade -
6 Trade Type Symbol New Shares
7 ADD AMD 25
8

I want my code to put a value in cell below "TotalShares" (D3) based on the
entry in the cell below Trade Type (A7).

If the Trade Type is:
"ADD", add New Shares to Old Shares.
"SELLPARTIAL", subtract New Shares from Old Shares
"SELLALL", subtract New Shares from Old Shares and enter "sold" in E3

Here is my code.
My problem is that I want it to execute the statements below Case Add but it
skips over that code. I don't know how to indicate that this is Case ADD.

How to I fix this? Thanks, Alan


Sub ProcessTrades()

Dim TrType As Range
Dim OldShares As Range
Dim TradeShares As Range
Dim TotalShares As Range

Set OldShares = Range("C3")
Set TradeShares = Range("C7")
Set TotalShares = Range("D3")
Set TrType = Range("A7")

Select Case TrType

Case Add
TotalShares.Value = OldShares.Value + TradeShares.Value

Case SellPartial
TotalShares.Value = OldShares.Value - TradeShares.Value

Case SellAll
TotalShares.Value = OldShares.Value - TradeShares.Value
Range(E3).Value = "sold"

End Select
End Sub


achidsey

Select Case
 

or this

Sub ProcessTrades()

Dim TrType As Range
Dim OldShares As Range
Dim TradeShares As Range
Dim TotalShares As Range

Set OldShares = Range("C3")
Set TradeShares = Range("C7")
Set TotalShares = Range("D3")
Set TrType = Range("A7")

If TrType = "Add" Then
TotalShares = OldShares + TradeShares
End If

If TrType = "SellPartial" Then
TotalShares = OldShares - TradeShares
End If

If TrType = "SellAll" Then
TotalShares = OldShares - TradeShares
Range("E3").Value = "sold"

End If
End Sub

--


Gary


"achidsey" (notmorespam) wrote in message
...
Thank you. That worked.
--
achidsey


"Wolf" wrote:

Try
case "Add" etc

Wolf

"achidsey" wrote:

I am trying to us a Select Case Construction but I am not able to get
the
program to choose the branch.

I have a spreadsheet similar to the following:
A B C D
E
1 Old
2 Symbol Shares TotalShares
3 AMD 50
4
5 - New Trade -
6 Trade Type Symbol New Shares
7 ADD AMD 25
8

I want my code to put a value in cell below "TotalShares" (D3) based on
the
entry in the cell below Trade Type (A7).

If the Trade Type is:
"ADD", add New Shares to Old Shares.
"SELLPARTIAL", subtract New Shares from Old Shares
"SELLALL", subtract New Shares from Old Shares and enter "sold"
in E3

Here is my code.
My problem is that I want it to execute the statements below Case Add
but it
skips over that code. I don't know how to indicate that this is Case
ADD.

How to I fix this? Thanks, Alan


Sub ProcessTrades()

Dim TrType As Range
Dim OldShares As Range
Dim TradeShares As Range
Dim TotalShares As Range

Set OldShares = Range("C3")
Set TradeShares = Range("C7")
Set TotalShares = Range("D3")
Set TrType = Range("A7")

Select Case TrType

Case Add
TotalShares.Value = OldShares.Value + TradeShares.Value

Case SellPartial
TotalShares.Value = OldShares.Value - TradeShares.Value

Case SellAll
TotalShares.Value = OldShares.Value - TradeShares.Value
Range(E3).Value = "sold"

End Select
End Sub


achidsey