+ Reply to Thread
Results 1 to 7 of 7

#Name? is Results

  1. #1
    Jim May
    Guest

    #Name? is Results

    Before running Macro3() below I select in my spreadsheet Range(G13:G42)
    then I switch sheets to Select a range from another sheet range(C64:AF64)
    At conclusion of Macro3() my Range(G13:G42) show #NAME?;;
    When I do this manually (with Record macro On) the argument within the
    Transpose function shows "=TRANSPOSE(Daily!R[51]C[-4]:R[51]C[25])"
    It works - NO PROBLEMS - But I can't understand R1C1 -

    Sub Macro3()
    '
    ' Macro3 Macro
    ' Macro recorded 5/30/2006 by Jim May
    Dim MySourceRng As Range
    Set MySourceRng = Application.InputBox("Point, Click and Highlite Source
    Range", Type:=8)
    Selection.FormulaArray = "=TRANSPOSE(MySourceRng(0,0))"
    End Sub

  2. #2
    Tom Ogilvy
    Guest

    RE: #Name? is Results

    Selection.FormulaArray = "=TRANSPOSE(MySourceRng(0,0))"

    should be

    Selection.FormulaArray = "=TRANSPOSE(" & _
    MySourceRng(1,1,xlA1,True)" & ")"

    --
    Regards,
    Tom Ogilvy


    "Jim May" wrote:

    > Before running Macro3() below I select in my spreadsheet Range(G13:G42)
    > then I switch sheets to Select a range from another sheet range(C64:AF64)
    > At conclusion of Macro3() my Range(G13:G42) show #NAME?;;
    > When I do this manually (with Record macro On) the argument within the
    > Transpose function shows "=TRANSPOSE(Daily!R[51]C[-4]:R[51]C[25])"
    > It works - NO PROBLEMS - But I can't understand R1C1 -
    >
    > Sub Macro3()
    > '
    > ' Macro3 Macro
    > ' Macro recorded 5/30/2006 by Jim May
    > Dim MySourceRng As Range
    > Set MySourceRng = Application.InputBox("Point, Click and Highlite Source
    > Range", Type:=8)
    > Selection.FormulaArray = "=TRANSPOSE(MySourceRng(0,0))"
    > End Sub


  3. #3
    Bob Phillips
    Guest

    Re: #Name? is Results

    Jim,

    Try this


    Sub Macro3()
    '
    ' Macro3 Macro
    ' Macro recorded 5/30/2006 by Jim May
    Dim MySourceRng As Range
    Set MySourceRng = Application.InputBox( _
    "Point, Click and Highlite Source Range", Type:=8)
    Selection.FormulaArray = "=TRANSPOSE(" & _
    MySourceRng.Address(ReferenceStyle:=xlR1C1, External:=True) & ")"
    End Sub



    --
    HTH

    Bob Phillips

    (replace somewhere in email address with gmail if mailing direct)

    "Jim May" <[email protected]> wrote in message
    news:[email protected]...
    > Before running Macro3() below I select in my spreadsheet Range(G13:G42)
    > then I switch sheets to Select a range from another sheet range(C64:AF64)
    > At conclusion of Macro3() my Range(G13:G42) show #NAME?;;
    > When I do this manually (with Record macro On) the argument within the
    > Transpose function shows "=TRANSPOSE(Daily!R[51]C[-4]:R[51]C[25])"
    > It works - NO PROBLEMS - But I can't understand R1C1 -
    >
    > Sub Macro3()
    > '
    > ' Macro3 Macro
    > ' Macro recorded 5/30/2006 by Jim May
    > Dim MySourceRng As Range
    > Set MySourceRng = Application.InputBox("Point, Click and Highlite

    Source
    > Range", Type:=8)
    > Selection.FormulaArray = "=TRANSPOSE(MySourceRng(0,0))"
    > End Sub




  4. #4
    Jim May
    Guest

    RE: #Name? is Results

    Thanks Tom:
    But I'm getting R/T error 450:
    Wrong number of arguments or invadid property assignment.
    See any needed changes?
    Jim

    "Tom Ogilvy" wrote:

    > Selection.FormulaArray = "=TRANSPOSE(MySourceRng(0,0))"
    >
    > should be
    >
    > Selection.FormulaArray = "=TRANSPOSE(" & _
    > MySourceRng(1,1,xlA1,True)" & ")"
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    > "Jim May" wrote:
    >
    > > Before running Macro3() below I select in my spreadsheet Range(G13:G42)
    > > then I switch sheets to Select a range from another sheet range(C64:AF64)
    > > At conclusion of Macro3() my Range(G13:G42) show #NAME?;;
    > > When I do this manually (with Record macro On) the argument within the
    > > Transpose function shows "=TRANSPOSE(Daily!R[51]C[-4]:R[51]C[25])"
    > > It works - NO PROBLEMS - But I can't understand R1C1 -
    > >
    > > Sub Macro3()
    > > '
    > > ' Macro3 Macro
    > > ' Macro recorded 5/30/2006 by Jim May
    > > Dim MySourceRng As Range
    > > Set MySourceRng = Application.InputBox("Point, Click and Highlite Source
    > > Range", Type:=8)
    > > Selection.FormulaArray = "=TRANSPOSE(MySourceRng(0,0))"
    > > End Sub


  5. #5
    Tom Ogilvy
    Guest

    RE: #Name? is Results

    Yes, I left the address property off and there was a residual double quote

    set MySourceRng = worksheets(2).Range("G64").Resize(1,42)

    ? "=TRANSPOSE(" & _
    MySourceRng.Address(1,1,xlA1,True) & ")"
    =TRANSPOSE([Book1]Sheet1!$G$64:$AV$64)

    for illustration.

    so


    Selection.FormulaArray = "=TRANSPOSE(" & _
    MySourceRng.Address(1,1,xlA1,True) & ")"

    --
    Regards,
    Tom Ogilvy



    "Jim May" wrote:

    > Thanks Tom:
    > But I'm getting R/T error 450:
    > Wrong number of arguments or invadid property assignment.
    > See any needed changes?
    > Jim
    >
    > "Tom Ogilvy" wrote:
    >
    > > Selection.FormulaArray = "=TRANSPOSE(MySourceRng(0,0))"
    > >
    > > should be
    > >
    > > Selection.FormulaArray = "=TRANSPOSE(" & _
    > > MySourceRng(1,1,xlA1,True)" & ")"
    > >
    > > --
    > > Regards,
    > > Tom Ogilvy
    > >
    > >
    > > "Jim May" wrote:
    > >
    > > > Before running Macro3() below I select in my spreadsheet Range(G13:G42)
    > > > then I switch sheets to Select a range from another sheet range(C64:AF64)
    > > > At conclusion of Macro3() my Range(G13:G42) show #NAME?;;
    > > > When I do this manually (with Record macro On) the argument within the
    > > > Transpose function shows "=TRANSPOSE(Daily!R[51]C[-4]:R[51]C[25])"
    > > > It works - NO PROBLEMS - But I can't understand R1C1 -
    > > >
    > > > Sub Macro3()
    > > > '
    > > > ' Macro3 Macro
    > > > ' Macro recorded 5/30/2006 by Jim May
    > > > Dim MySourceRng As Range
    > > > Set MySourceRng = Application.InputBox("Point, Click and Highlite Source
    > > > Range", Type:=8)
    > > > Selection.FormulaArray = "=TRANSPOSE(MySourceRng(0,0))"
    > > > End Sub


  6. #6
    Jim May
    Guest

    RE: #Name? is Results

    Thanks for the Clarification.
    Jim

    "Tom Ogilvy" wrote:

    > Yes, I left the address property off and there was a residual double quote
    >
    > set MySourceRng = worksheets(2).Range("G64").Resize(1,42)
    >
    > ? "=TRANSPOSE(" & _
    > MySourceRng.Address(1,1,xlA1,True) & ")"
    > =TRANSPOSE([Book1]Sheet1!$G$64:$AV$64)
    >
    > for illustration.
    >
    > so
    >
    >
    > Selection.FormulaArray = "=TRANSPOSE(" & _
    > MySourceRng.Address(1,1,xlA1,True) & ")"
    >
    > --
    > Regards,
    > Tom Ogilvy
    >
    >
    >
    > "Jim May" wrote:
    >
    > > Thanks Tom:
    > > But I'm getting R/T error 450:
    > > Wrong number of arguments or invadid property assignment.
    > > See any needed changes?
    > > Jim
    > >
    > > "Tom Ogilvy" wrote:
    > >
    > > > Selection.FormulaArray = "=TRANSPOSE(MySourceRng(0,0))"
    > > >
    > > > should be
    > > >
    > > > Selection.FormulaArray = "=TRANSPOSE(" & _
    > > > MySourceRng(1,1,xlA1,True)" & ")"
    > > >
    > > > --
    > > > Regards,
    > > > Tom Ogilvy
    > > >
    > > >
    > > > "Jim May" wrote:
    > > >
    > > > > Before running Macro3() below I select in my spreadsheet Range(G13:G42)
    > > > > then I switch sheets to Select a range from another sheet range(C64:AF64)
    > > > > At conclusion of Macro3() my Range(G13:G42) show #NAME?;;
    > > > > When I do this manually (with Record macro On) the argument within the
    > > > > Transpose function shows "=TRANSPOSE(Daily!R[51]C[-4]:R[51]C[25])"
    > > > > It works - NO PROBLEMS - But I can't understand R1C1 -
    > > > >
    > > > > Sub Macro3()
    > > > > '
    > > > > ' Macro3 Macro
    > > > > ' Macro recorded 5/30/2006 by Jim May
    > > > > Dim MySourceRng As Range
    > > > > Set MySourceRng = Application.InputBox("Point, Click and Highlite Source
    > > > > Range", Type:=8)
    > > > > Selection.FormulaArray = "=TRANSPOSE(MySourceRng(0,0))"
    > > > > End Sub


  7. #7
    Jim May
    Guest

    Re: #Name? is Results

    Bob:
    Thanks A LOT !!

    "Bob Phillips" wrote:

    > Jim,
    >
    > Try this
    >
    >
    > Sub Macro3()
    > '
    > ' Macro3 Macro
    > ' Macro recorded 5/30/2006 by Jim May
    > Dim MySourceRng As Range
    > Set MySourceRng = Application.InputBox( _
    > "Point, Click and Highlite Source Range", Type:=8)
    > Selection.FormulaArray = "=TRANSPOSE(" & _
    > MySourceRng.Address(ReferenceStyle:=xlR1C1, External:=True) & ")"
    > End Sub
    >
    >
    >
    > --
    > HTH
    >
    > Bob Phillips
    >
    > (replace somewhere in email address with gmail if mailing direct)
    >
    > "Jim May" <[email protected]> wrote in message
    > news:[email protected]...
    > > Before running Macro3() below I select in my spreadsheet Range(G13:G42)
    > > then I switch sheets to Select a range from another sheet range(C64:AF64)
    > > At conclusion of Macro3() my Range(G13:G42) show #NAME?;;
    > > When I do this manually (with Record macro On) the argument within the
    > > Transpose function shows "=TRANSPOSE(Daily!R[51]C[-4]:R[51]C[25])"
    > > It works - NO PROBLEMS - But I can't understand R1C1 -
    > >
    > > Sub Macro3()
    > > '
    > > ' Macro3 Macro
    > > ' Macro recorded 5/30/2006 by Jim May
    > > Dim MySourceRng As Range
    > > Set MySourceRng = Application.InputBox("Point, Click and Highlite

    > Source
    > > Range", Type:=8)
    > > Selection.FormulaArray = "=TRANSPOSE(MySourceRng(0,0))"
    > > End Sub

    >
    >
    >


+ 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