How to create a variable from a calculation
 

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

--
achidsey

How to create a variable from a calculation
 

The following modification works.........' changes required
highlighted..........


Sub CalcSellPct()

Dim TOpenPos As Range
Dim TShares As Range

Dim Trades As Range '<==== add reference
Dim Trade As Range '<==== add reference

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

'<===== Change formula to relative reference =====
Cells(Trade.Row, TShares.Column + 1).FormulaR1C1 = _
Cells(Trade.Row, TShares.Column) / Cells(Trade.Row,
TOpenPos.Column)

Next Trade

End Sub

--
Cheers
Nigel



"achidsey" (notmorespam) wrote in message
...

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

--
achidsey

How to create a variable from a calculation
 

Nigel,

Thanks for the assistance.

Alan
--
achidsey


"Nigel" wrote:

The following modification works.........' changes required
highlighted..........


Sub CalcSellPct()

Dim TOpenPos As Range
Dim TShares As Range

Dim Trades As Range '<==== add reference
Dim Trade As Range '<==== add reference

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

'<===== Change formula to relative reference =====
Cells(Trade.Row, TShares.Column + 1).FormulaR1C1 = _
Cells(Trade.Row, TShares.Column) / Cells(Trade.Row,
TOpenPos.Column)

Next Trade

End Sub

--
Cheers
Nigel



"achidsey" (notmorespam) wrote in message
...

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

--
achidsey