Consulting

Results 1 to 8 of 8

Thread: Solved: Formula in VBA code - modification requested to avoid Error

  1. #1
    VBAX Regular
    Joined
    May 2008
    Location
    India
    Posts
    35
    Location

    Smile Solved: Formula in VBA code - modification requested to avoid Error

    Dear Friends,

    I have a macro to do a process in which the following code appears.

    .FormulaR1C1 = "=SUM(R[-1]C/R[-1]C5*100)"
    When it is run, if the value is not there, then it shows the #DIV0! Error.
    Can any one help me to avoid this Error.

    Generally, in worksheet we apply the = IF(ISERROR(SUM(......) formula. I don't know how to apply this kind of formula with the above code.

    Your kind help is very much needed. Please....

    Thanks in advance.

    acsishere.

  2. #2
    VBAX Mentor
    Joined
    Oct 2007
    Posts
    372
    Location
    .FormulaR1C1 = "=if(iserror(SUM(R[-1]C/R[-1]C5*100),"",SUM(R[-1]C/R[-1]C5*100)"

    This will just leave it blank if it would normally say #div0

  3. #3
    VBAX Mentor
    Joined
    Oct 2007
    Posts
    372
    Location
    fyi -- it's the exact same application as you mentioned!

    if that works, don't forget to mark solved in thread tools...

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    [vba]

    .FormulaR1C1 = "=IF(R[-1]C5=0,"""",SUM(R[-1]C/R[-1]C5*100))"
    [/vba]
    Last edited by Bob Phillips; 05-22-2008 at 01:16 PM.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Regular
    Joined
    May 2008
    Location
    India
    Posts
    35
    Location

    Smile Help please!!

    Dear Grichey,

    Thanks for your reply. I appied the following code, as you said:

    .FormulaR1C1 = "=IF(ISERROR(SUM(R[-1]C/R[-1]C5*100),"",SUM(R[-1]C/R[-1]C5*100))"
    It does nothing. I think because of the inverted commas (""). In formula it leaves blank. Whereas in vba the inverted commas are playing different role. Hence, the solution please.

    Thanks in advance.

    acsishere.

  6. #6
    VBAX Mentor
    Joined
    Oct 2007
    Posts
    372
    Location
    Sorry as xld mentioned. It should be """"

  7. #7
    VBAX Regular
    Joined
    May 2008
    Location
    India
    Posts
    35
    Location

    Thumbs up Solved: Formula in VBA code - modification requested to avoid Error

    That's nice. Thanks a lot for you both.

    Though it is a small code, it relieved me from a lot of trouble.

    Thanks again,

    acsishere.
    Last edited by acsishere; 05-23-2008 at 08:16 AM. Reason: It is solved.

  8. #8
    VBAX Mentor
    Joined
    Oct 2007
    Posts
    372
    Location
    The solved function is at the top under 'thread tools' just above your first post. just fyi

Posting Permissions

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