Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    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.

  2. #2
    Plutonium Lounger
    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)"

  3. #3
    3 Star Lounger
    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?

  4. #4
    Plutonium Lounger
    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

  5. #5
    3 Star Lounger
    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.

  6. #6
    Plutonium Lounger
    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.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    3 Star Lounger
    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 (5-50%). 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.

  9. #9
    3 Star Lounger
    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.

  10. #10
    Plutonium Lounger
    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.

  11. #11
    3 Star Lounger
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •