#Name? is Results

[Guest]

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

 

[Guest]

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

should be

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

 

[Bob Phillips]

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)

 

[Guest]

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

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

should be

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

--
Regards,
Tom Ogilvy

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

 

[Guest]

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

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

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

should be

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

--
Regards,
Tom Ogilvy

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

 

[Guest]

Thanks for the Clarification.
Jim

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

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

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

should be

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

--
Regards,
Tom Ogilvy


:

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

 

[Guest]

Bob:
Thanks A LOT !!

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)

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