Unable to populate a range with a formula
in [Programming]
|
Prev: Launching PowerPoint from an Excel application
Next: MSCOMCT2.OCX registration error "ClassFactory cannot supply requested class"
From: Joel on 7 Oct 2008 15:48 The parethesis is definetly needed. If you are still gettting #NAME then is can't find the function. With Excel 2003 this is usually becvause the function has been put into the wrong type Page in the VPA Project or is in the wrong workbook (like personal.xls). Make sure you put the Function into a MODULE sheet. "MichaelDavid" wrote: > Another possibility: Perhaps, with certain Excel setups, one has to register > a new User Defined Function with Excel. > -- > May you have a most blessed day! > > Sincerely, > > Michael Fitzpatrick > > > "MichaelDavid" wrote: > > > Greetings! This one has me stumped. I am trying to populate a range with a > > formula. The code is: > > > > Sub TestSetAC2() > > Range("AC2:AC10").Formula = "=SetAC2" > > Exit Sub > > End Sub > > > > Function SetAC2() > > SetAC2 = 5 > > End Function > > > > First I opened a blank worksheet. When I execute the procedure, I get #Name? > > thruout the range. The tool tip just to the left of #Name? says that the > > formula contains unrecognized text. Any help will be greatly appreciated. > > -- > > May you have a most blessed day! > > > > Sincerely, > > > > Michael Fitzpatrick
From: Dave Peterson on 7 Oct 2008 16:00 If that setac2 function is in the the same workbook with TestSetAc2, but not the blank workbook you just opened, then you'll want to specify where to find that function: Sub TestSetAC2() activesheet.Range("AC2:AC10").Formula _ = "='" & thisworkbook.name & "'!SetAC2()" End Sub MichaelDavid wrote: > > Greetings! This one has me stumped. I am trying to populate a range with a > formula. The code is: > > Sub TestSetAC2() > Range("AC2:AC10").Formula = "=SetAC2" > Exit Sub > End Sub > > Function SetAC2() > SetAC2 = 5 > End Function > > First I opened a blank worksheet. When I execute the procedure, I get #Name? > thruout the range. The tool tip just to the left of #Name? says that the > formula contains unrecognized text. Any help will be greatly appreciated. > -- > May you have a most blessed day! > > Sincerely, > > Michael Fitzpatrick -- Dave Peterson
From: MichaelDavid on 7 Oct 2008 16:15 Hi Dave: Thru extensive trial and error, I got this to work. Here is the solution: Sub TestSetAC2() Range("A2:A10").Formula = SetAC2 Exit Sub End Sub Function SetAC2() SetAC2 = 5 End Function In other words, we need to use: Range("A2:A10").Formula = SetAC2 and not: Range("AC2:AC10").Formula = "=SetAC2" But why? Does anyone on this group know? (Perh. it has something to do with whether one uses .Formula or .FormulaR1C1.) -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Dave Peterson" wrote: > If that setac2 function is in the the same workbook with TestSetAc2, but not the > blank workbook you just opened, then you'll want to specify where to find that > function: > > Sub TestSetAC2() > activesheet.Range("AC2:AC10").Formula _ > = "='" & thisworkbook.name & "'!SetAC2()" > End Sub > > MichaelDavid wrote: > > > > Greetings! This one has me stumped. I am trying to populate a range with a > > formula. The code is: > > > > Sub TestSetAC2() > > Range("AC2:AC10").Formula = "=SetAC2" > > Exit Sub > > End Sub > > > > Function SetAC2() > > SetAC2 = 5 > > End Function > > > > First I opened a blank worksheet. When I execute the procedure, I get #Name? > > thruout the range. The tool tip just to the left of #Name? says that the > > formula contains unrecognized text. Any help will be greatly appreciated. > > -- > > May you have a most blessed day! > > > > Sincerely, > > > > Michael Fitzpatrick > > -- > > Dave Peterson >
From: MichaelDavid on 7 Oct 2008 16:52 Greetings! I spoke to quickly. Range("A2:A10").Formula = SetAC2 only sets the value of the function, and does not propagate the function call thruout the range. It is beginning to look like calls to functions which set the function to a value can not be used in a worksheet. I was hoping that something like: Range("AC2:AC10").Formula = "=SetAC2" would work, but it just gives #Name? thruout the range. I am hoping that someone in this group will show me the correct way to propagate a function call thruout a range. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "MichaelDavid" wrote: > Hi Dave: > Thru extensive trial and error, I got this to work. Here is the solution: > > Sub TestSetAC2() > > Range("A2:A10").Formula = SetAC2 > Exit Sub > End Sub > > Function SetAC2() > > SetAC2 = 5 > End Function > > In other words, we need to use: > Range("A2:A10").Formula = SetAC2 > and not: > Range("AC2:AC10").Formula = "=SetAC2" > > But why? Does anyone on this group know? (Perh. it has something to do with > whether one uses .Formula or .FormulaR1C1.) > -- > May you have a most blessed day! > > Sincerely, > > Michael Fitzpatrick > > > "Dave Peterson" wrote: > > > If that setac2 function is in the the same workbook with TestSetAc2, but not the > > blank workbook you just opened, then you'll want to specify where to find that > > function: > > > > Sub TestSetAC2() > > activesheet.Range("AC2:AC10").Formula _ > > = "='" & thisworkbook.name & "'!SetAC2()" > > End Sub > > > > MichaelDavid wrote: > > > > > > Greetings! This one has me stumped. I am trying to populate a range with a > > > formula. The code is: > > > > > > Sub TestSetAC2() > > > Range("AC2:AC10").Formula = "=SetAC2" > > > Exit Sub > > > End Sub > > > > > > Function SetAC2() > > > SetAC2 = 5 > > > End Function > > > > > > First I opened a blank worksheet. When I execute the procedure, I get #Name? > > > thruout the range. The tool tip just to the left of #Name? says that the > > > formula contains unrecognized text. Any help will be greatly appreciated. > > > -- > > > May you have a most blessed day! > > > > > > Sincerely, > > > > > > Michael Fitzpatrick > > > > -- > > > > Dave Peterson > >
From: Dave Peterson on 7 Oct 2008 17:36
What happened when you tried my suggestion? Did it work or did it fail? MichaelDavid wrote: > > Greetings! I spoke to quickly. Range("A2:A10").Formula = SetAC2 only sets the > value of the function, and does not propagate the function call thruout the > range. It is beginning to look like calls to functions which set the function > to a value can not be used in a worksheet. I was hoping that something like: > Range("AC2:AC10").Formula = "=SetAC2" would work, but it just gives #Name? > thruout the range. I am hoping that someone in this group will show me the > correct way to propagate a function call thruout a range. > -- > May you have a most blessed day! > > Sincerely, > > Michael Fitzpatrick > > "MichaelDavid" wrote: > > > Hi Dave: > > Thru extensive trial and error, I got this to work. Here is the solution: > > > > Sub TestSetAC2() > > > > Range("A2:A10").Formula = SetAC2 > > Exit Sub > > End Sub > > > > Function SetAC2() > > > > SetAC2 = 5 > > End Function > > > > In other words, we need to use: > > Range("A2:A10").Formula = SetAC2 > > and not: > > Range("AC2:AC10").Formula = "=SetAC2" > > > > But why? Does anyone on this group know? (Perh. it has something to do with > > whether one uses .Formula or .FormulaR1C1.) > > -- > > May you have a most blessed day! > > > > Sincerely, > > > > Michael Fitzpatrick > > > > > > "Dave Peterson" wrote: > > > > > If that setac2 function is in the the same workbook with TestSetAc2, but not the > > > blank workbook you just opened, then you'll want to specify where to find that > > > function: > > > > > > Sub TestSetAC2() > > > activesheet.Range("AC2:AC10").Formula _ > > > = "='" & thisworkbook.name & "'!SetAC2()" > > > End Sub > > > > > > MichaelDavid wrote: > > > > > > > > Greetings! This one has me stumped. I am trying to populate a range with a > > > > formula. The code is: > > > > > > > > Sub TestSetAC2() > > > > Range("AC2:AC10").Formula = "=SetAC2" > > > > Exit Sub > > > > End Sub > > > > > > > > Function SetAC2() > > > > SetAC2 = 5 > > > > End Function > > > > > > > > First I opened a blank worksheet. When I execute the procedure, I get #Name? > > > > thruout the range. The tool tip just to the left of #Name? says that the > > > > formula contains unrecognized text. Any help will be greatly appreciated. > > > > -- > > > > May you have a most blessed day! > > > > > > > > Sincerely, > > > > > > > > Michael Fitzpatrick > > > > > > -- > > > > > > Dave Peterson > > > -- Dave Peterson |