About Us

achidsey

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

Thread Tools	Search this Thread
Show Printable Version	Search this Thread: Advanced Search
Display Modes
Switch to Linear Mode Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Variable calculation question	EG	Excel Worksheet Functions	1	October 19th 09 12:00 AM
How do I create the following calculation based on volume	ckightly	Excel Discussion (Misc queries)	1	December 4th 07 02:03 AM
create new variable	Angie	Excel Worksheet Functions	4	January 23rd 07 08:26 PM
create time calculation formula	Jcraig713	Excel Discussion (Misc queries)	3	June 28th 06 01:24 PM
Variable time calculation	Issam LAdki	New Users to Excel	6	April 7th 05 04:05 PM

Menu

About Us