Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
SetAC2 is a function and need the parethesis
Range("AC2:AC10").Formula = "=SetAC2()" "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
Hi Joel:
I added () like you suggested, and still get #Name? populated thruout the range. What should I try next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Joel" wrote: SetAC2 is a function and need the parethesis Range("AC2:AC10").Formula = "=SetAC2()" "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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
By the way, I tried the example on page 18 of "Excel 2007 VBA Programmer's
Reference" (ISBN 978-0-470-04643-2). The example is the creation of a UDF (User Defined Function) to convert degrees Centigrade to degrees Fahrenheit. The code is as follows, and I placed it in a module in my Personal Macro Notebook: Function Fahrenheit(Centigrade) Fahrenheit = Centigrade * 9 / 5 + 32 End Function Column A of the worksheet has the Centigrade values to be converted, and Column B is to have the resultant Fahrenheit degrees. When I set B2 on the worksheet to =Fahrenheit(A2) as per the example in the book, B2 displays as #Name?. Perhaps there is a setting in my Excel setup which prohibits setting B2 in this way. I tried this example also with Excel 2003 on another computer with the same results. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "MichaelDavid" wrote: Hi Joel: I added () like you suggested, and still get #Name? populated thruout the range. What should I try next? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Joel" wrote: SetAC2 is a function and need the parethesis Range("AC2:AC10").Formula = "=SetAC2()" "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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
Hi Dave:
PROBLEM SOLVED! The code for a UDF should be placed in a standard code module, not in one of the Sheet modules, not in the ThisWorkbook module, and not in the Personal Workbook Module. I had the code in my Personal Workbook Module. What I did: In the VBA editor, I went to the Insert menu and chose Module. A new code module was inserted into the project. Then I added the following code: Sub GetConv2() Range("F2:F12").Formula = "=Fahrenheit(E2)" Exit Sub End Sub Function Fahrenheit(Centigrade) Fahrenheit = Centigrade * 9 / 5 + 32 End Function The Worksheet had the following in columns E and F: E F Centigrade Fahrenheit 0 10 20 30 40 50 60 70 80 90 100 I then executed Sub GetConv2(). The result is copied below: E F Centigrade Fahrenheit 0 32 10 50 20 68 30 86 40 104 50 122 60 140 70 158 80 176 90 194 100 212 I really did not think we would win this one. To think that my mistake is that I placed the code for the function in the Personal Workbook rather than in a Standard Code Module. Microsoft should be scolded for their unhelpful error messages. Something simple like: "Don't use the Personal Workbook for User Defined Functions" would have save me about 12 hours of research, trial, and error. Who would have guessed that it was all a matter of which module you put your code into? -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Dave Peterson" wrote: 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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to populate a range with a formula
We both came up with the solution at about the same time (12:47 PM vs 12:55
PM PST), but I didn't get a chance to read your posting until just now (5:25 PM PST). As I mentioned in the post I wrote when I finally solved the problem: "PROBLEM SOLVED! The code for a UDF should be placed in a standard code module, not in one of the Sheet modules, not in the ThisWorkbook module, and not in the Personal Workbook Module. I had the code in my Personal Workbook Module. What I did: In the VBA editor, I went to the Insert menu and chose Module. A new code module was inserted into the project. Then I added the following code: Sub GetConv2() Range("F2:F12").Formula = "=Fahrenheit(E2)" Exit Sub End Sub Function Fahrenheit(Centigrade) Fahrenheit = Centigrade * 9 / 5 + 32 End Function The Worksheet had the following in columns E and F: E F Centigrade Fahrenheit 0 10 20 30 40 50 60 70 80 90 100 I then executed Sub GetConv2(). The result is copied below: E F Centigrade Fahrenheit 0 32 10 50 20 68 30 86 40 104 50 122 60 140 70 158 80 176 90 194 100 212 I really did not think we would win this one. To think that my mistake is that I placed the code for the function in the Personal Workbook rather than in a Standard Code Module. Microsoft should be scolded for their unhelpful error messages. Something simple like: "Don't use the Personal Workbook for User Defined Functions" would have save me about 12 hours of research, trial, and error. Who would have guessed that it was all a matter of which module you put your code into? Thank you very much for your post. -- May you have a most blessed day! Sincerely, Michael Fitzpatrick "Joel" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to Delete blanks between a range and populate only the names inthe given range | Excel Discussion (Misc queries) | |||
unable to set the formula array property of the range class | Excel Worksheet Functions | |||
Populate Range | Excel Programming | |||
How to populate formula in range of vertical cells to next colum | Excel Worksheet Functions | |||
using For each loop to populate a range | Excel Programming |