+ Reply to Thread
Results 1 to 3 of 3

How to create a variable from a calculation

Hybrid View

Guest How to create a variable from... 09-25-2005, 02:05 AM
Guest Re: How to create a variable... 09-25-2005, 03:05 AM
Guest Re: How to create a variable... 09-25-2005, 04:05 AM
  1. #1
    achidsey
    Guest

    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

  2. #2
    Nigel
    Guest

    Re: 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" <chidsey2@hotmail.com(notmorespam)> wrote in message
    news:FD0374FB-B4B5-44B0-A066-542DF9407164@microsoft.com...
    >
    > 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




  3. #3
    achidsey
    Guest

    Re: 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" <chidsey2@hotmail.com(notmorespam)> wrote in message
    > news:FD0374FB-B4B5-44B0-A066-542DF9407164@microsoft.com...
    > >
    > > 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

    >
    >
    >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1