Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > #Name? is Results
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 477
Default #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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default #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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default #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


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 477
Default #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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,953
Default #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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 477
Default #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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 477
Default #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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
v-look up - no results Kerstin Excel Worksheet Functions 5 October 30th 07 10:11 PM
#NAME? and other results. Narendra Boga[_2_] Excel Discussion (Misc queries) 3 July 21st 07 01:26 PM
Conditional Sum Argument results do not equal cell results Excel Randy R Mullins Excel Worksheet Functions 3 August 9th 06 07:16 PM
Wanting to write results to array instead of sheet, results overwriting.... [email protected] Excel Programming 2 October 31st 05 01:47 PM
How can I list the results of my macro without overwritng previous results? mattip Excel Programming 3 November 28th 03 03:45 AM


All times are GMT +1. The time now is 10:13 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement