Thanks Thanks:  0
Likes Likes:  0
Results 1 to 9 of 9

Thread: Summing in VBA - Still Not Working!!!

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Nanaimo, BC
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Range("A5").Select
    ActiveCell.FormulaR1C1 = SUM(A1:A4)

    Trying to do something like this but the compiler doesn't like the syntax... any suggestions?
    Thanks,
    Kyle

    [ This Message was edited by: kwaring on 2002-03-24 17:13 ]

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-24 15:25, kwaring wrote:
    Range("A5").Select
    ActiveCell.FormulaR1C1 = SUM(A1:A4)

    Trying to do something like this but the compiler doesn't like the syntax... any suggestions?
    Thanks,
    Kyle
    Hi Kyle,

    Be sure to enclose the formula to load to the worksheet in quotes. Also, no need to select unles required, as it makes the code inefficient.

    Range("A5") = "=SUM(A1:A4)"

    would work for you.

    Bye,
    Jay

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try

    ActiveCell.Formula = "=Sum(A1:A4)"

    FormulaR1C1 expects a formula in relative reference format e.g. =Sum(r2c1,r3c4). For both you need to assign a string as the formula you want to use.

    HTH,
    D

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    Nanaimo, BC
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Jay and dk, however, I am still having problems... Your code works on the simple examples above and I thought I could incorporate that into my more-complex code but it still doesn't work.

    Range("E" & Search) = "=SUM("E" & Start & ":" & "E" & Search)"

    Where Search and Start are variables defined in VBA. When I try to run the macro, I get a Run-time error '1004': Application-defined or object-defined error
    Thanks,
    Kyle

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    Nanaimo, BC
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Actually looks like this:

    Range("E" & Search + 1) = "=SUM("E" & Start & ":" & "E" & Search)"

    I've tried:
    Range("E" & Search + 1) = "=SUM('E' & Start & ':' & 'E' & Search)"

    Still no luck

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Kwaring

    Try

    Range("E" & Search + 1) = "=SUM(E" & Start & ":" & "E" & Search & ")"

  7. #7
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-24 15:25, kwaring wrote:
    Range("A5").Select
    ActiveCell.FormulaR1C1 = SUM(A1:A4)

    Trying to do something like this but the compiler doesn't like the syntax... any suggestions?
    Thanks,
    Kyle

    [ This Message was edited by: kwaring on 2002-03-24 17:13 ]
    Hey Kyle:
    Referring to the formula in your original posting, change it to:

    Sub RangeAdd2()
    Range("A5").Select
    ActiveCell.Formula = "=sum(a1:a4)"
    End Sub

    Put the above sub in a module of your workbook. It works. The suggestions by Jay and dk given to you earlier do work.
    So please check:
    1) Install the SUB in a module of the Active Workbook
    2) Check the SUB for editorial/syntax issues
    3) Run the SUB

    After you do the above post back what you get, VBA GURUs like Jay, dk, and Dave will be able to look at your SUB and point out where is what wrong.

    _________________
    Yogi Anand
    Edit: Deleted inactive web site reference from hard coded signature

    [ This Message was edited by: Yogi Anand on 2003-01-19 15:17 ]

  8. #8
    New Member
    Join Date
    Mar 2002
    Location
    Nanaimo, BC
    Posts
    49
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That works perfectly, Dave... THANK YOU!

  9. #9
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-03-24 16:45, kwaring wrote:
    Thanks Jay and dk, however, I am still having problems... Your code works on the simple examples above and I thought I could incorporate that into my more-complex code but it still doesn't work.

    Range("E" & Search) = "=SUM("E" & Start & ":" & "E" & Search)"

    Where Search and Start are variables defined in VBA. When I try to run the macro, I get a Run-time error '1004': Application-defined or object-defined error
    Thanks,
    Kyle
    Hi Kyle:
    Sorry about my last belated post. In reference to the formula, try the following formula:

    ="Range(""E"&search+1&""")=""=sum(E"&start&":E"&search&")"""

    results in
    Range("E5")="=sum(E1:E4)"

    HTH

    _________________
    Yogi Anand
    Edit: Deleted inactive web site reference from hard coded signature

    [ This Message was edited by: Yogi Anand on 2003-01-19 15:18 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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