A
achidsey
Excel Experts
I'm new to trying to use a variable that is a value.
My spreadsheet is similar to the following,
A B C
1
2 New Trades
3 TSymbol TOpenPos TShares
4 AMD 1000 500
5 INTC 2500 1000
I want to create a variable, SellPct, calculated as TShares/TOpenPos.
So for AMD, SellPct = .5 (500/1000)
The code I tried is:
Sub CalcSellPct()
Dim TOpenPos As Range
Dim TShares As Range
Set TOpenPos = Cells.Find(What:="TOpenPos")
Set TShares = Cells.Find(What:="TShares")
Cells.Find(What:="New Trades").Select
Selection.Offset(2).Select
Range(Selection, Selection.End(xlDown)).Select
Set Trades = Selection
For Each Trade In Trades
Set SellPct = Cells(Trade.Row, TShares.Column) /
Cells(Trade.Row, TOpenPos.Column)
Range("A1").FormulaR1C1 = SellPct
Next Trade
End Sub
When I run this I get a TypeMismatch error message on the line,
Set SellPct = Cells(Trade.Row, TShares.Column) / Cells(Trade.Row,
TOpenPos.Column)
I am able to use the line "Set SellPct = Cells(Trade.Row, TShares.Column)"
What do I need to change?
Thanks in advance,
Alan
I'm new to trying to use a variable that is a value.
My spreadsheet is similar to the following,
A B C
1
2 New Trades
3 TSymbol TOpenPos TShares
4 AMD 1000 500
5 INTC 2500 1000
I want to create a variable, SellPct, calculated as TShares/TOpenPos.
So for AMD, SellPct = .5 (500/1000)
The code I tried is:
Sub CalcSellPct()
Dim TOpenPos As Range
Dim TShares As Range
Set TOpenPos = Cells.Find(What:="TOpenPos")
Set TShares = Cells.Find(What:="TShares")
Cells.Find(What:="New Trades").Select
Selection.Offset(2).Select
Range(Selection, Selection.End(xlDown)).Select
Set Trades = Selection
For Each Trade In Trades
Set SellPct = Cells(Trade.Row, TShares.Column) /
Cells(Trade.Row, TOpenPos.Column)
Range("A1").FormulaR1C1 = SellPct
Next Trade
End Sub
When I run this I get a TypeMismatch error message on the line,
Set SellPct = Cells(Trade.Row, TShares.Column) / Cells(Trade.Row,
TOpenPos.Column)
I am able to use the line "Set SellPct = Cells(Trade.Row, TShares.Column)"
What do I need to change?
Thanks in advance,
Alan