Results 1 to 11 of 11
Thread: VBA formula (2003 SP2)

20080208, 14:29 #1
 Join Date
 Nov 2001
 Posts
 342
 Thanks
 0
 Thanked 0 Times in 0 Posts
VBA formula (2003 SP2)
I create a formula in a cell using the following code:
ActiveCell.Formula = "=if(sum("ae5:au5")>0,1,0)"
I then have to copy it down 700 rows. When it is finished each cell comes up with a #value error. The thing is, if I simply edit a cell, without changing anything, and hit enter, the formula calculates correctly.
How can I just get the formula to calculate correctly using the code?
Many thanks for your help.

20080208, 14:40 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VBA formula (2003 SP2)
That line shouldn't work at all  it should give an error message when you try to run it.
Does this work?
ActiveCell.Formula = "=IF(SUM(AE5:AU5)>0,1,0)"

20080209, 00:19 #3
 Join Date
 Nov 2001
 Posts
 342
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VBA formula (2003 SP2)
Apologies, Hans, I misdiagnosed the cause of my error.
I have a function as follows, designed to identify if a cell contains a formula or not:
Function ISFormula(Target As Range)
ISFormula = Target.HasFormula
End Function
In a cell I have the formula =IF(ISFormula(P5),0,1), which works fine. If P5 has a formula, the result is 0, otherwise it's a 1.
Because the range that I want to test for formulas may change, I have code to copy the formula over a set of cells. This works OK, however once the code finishes all the cells  including the original cell that the formula is copied from  are replaced with #value. If I edit one of the formulas and hit the enter button it corrects itself. If I do something unrelated like delete a column, all the cells correct themselves. Yet if I hit F9 to recalculate, they don't.
Is there a way to correct this?

20080209, 00:35 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VBA formula (2003 SP2)
How exactly are you copying the formulas? If I copy a cell with the formula to a range:
Range("B1").Copy Destination:=Range("B2:B12")
I see the problem you describe. As a workaround, you can set the formulas explicitly:
Range("B1:B12").Formula = "=IF(IsFormula(A1),1,0)"
or copy the formula
Range("B1:B12").Formula = Range("B1").Formula

20080209, 01:45 #5
 Join Date
 Nov 2001
 Posts
 342
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VBA formula (2003 SP2)
Thanks Hans, your workaround works fine.
However how would this work for a range?
For example, I tried the following, where 'picky' is a variable and 'copyfrom' is a worksheet range name:
Range(picky).Formula = Range("copyfrom").Formula
which gave a 'method range of global object failed' error, so I tried:
aspy = Range("copyfrom")
Range(picky).Formula = Range(aspy).Formula
which also generated an error. Does this work for ranges?
Thanks again.

20080209, 01:55 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VBA formula (2003 SP2)
It probably depends on the definition of the ranges. It worked OK with named ranges specified by string variables in a small test.
Why do you want to do this in code? Wouldn't it be easier to do this interactively? After all, you only have to do it once.

20080209, 02:02 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: VBA formula (2003 SP2)
What is the contents of the variable "picky"?
What does the named range "CopyFrom" range refer to?
Steve

20080209, 02:07 #8
 Join Date
 Nov 2001
 Posts
 342
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VBA formula (2003 SP2)
The worksheet is a product costing table that shows various markups (550%). These are all formulas, however sometimes they wish to lock in a fixed price. This is why I need to test the range of cells to see if they are formulas or not so as to not overwrite them with formulas again. As they can add or remove columns from time to time, I have to do this in code to ensure I capture the correct range. My code identifies the number of columns and then copies the master formula across a set of columns to determine where the fixed prices are.
I don't like this approach but that is what they want.
I appreciate your help.

20080209, 02:18 #9
 Join Date
 Nov 2001
 Posts
 342
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VBA formula (2003 SP2)
Hi Seve
picky is a variable that contains "AE7:AU708"
Copyfrom is a worksheet range  I think this may be where the problem is  the worksheet did not create it:
aspy = "=sheet1!R7C" & colst & ":R7C" & colend  2 'colst and colend are variables that define the column number of the range I want to copy. Row 7 is the row I want to copy.
ActiveWorkbook.Names.Add Name:="copyfrom", RefersToR1C1:=aspy 'This is where the code is tripping up  the range "copyfrom" is not being created.
aspy = Range("copyfrom")
Do you have a solution to my problem?
Much appreciated.

20080209, 03:43 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 29 Times in 29 Posts
Re: VBA formula (2003 SP2)
I don't see a problem with the line defining the named range (it works on my PC if I supply appropriate values for colst and colend), but if you want to assign the range to aspy, you must use the keyword Set:
Set aspy = Range("copyfrom")
But then, you can't use
Range(picky).Formula = Range(aspy).Formula
because aspy is no longer a string value but a range. You'd need
Range(picky).Formula = aspy.Formula
But I think that only works if aspy contains a single cell.
Instead of trying to do it in such a complicated way, with confusing variables, I'd set the formulas directly.

20080212, 09:45 #11
 Join Date
 Nov 2001
 Posts
 342
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: VBA formula (2003 SP2)
Thank you all, I managed to find a solution to my problem using a combination of the solutions you offered above.
Thanks again.