ExcelBanter - #Name? is Results

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - #Name? is Results (https://www.excelbanter.com/excel-programming/362818-name-results.html)

Jim May

#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

Tom Ogilvy

#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

Bob Phillips

#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" wrote in message
...
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

Jim May

#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

Tom Ogilvy

#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

Jim May

#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

Jim May

#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" wrote in message
...
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

All times are GMT +1. The time now is 04:21 AM.