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
|