1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Microsoft Excel 2003 Sum data help needed

Discussion in 'Business Applications' started by Strybes, Mar 30, 2010.

Thread Status:
Not open for further replies.
  1. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Hi All

    Thanks for your help.

    I am trying to write a macro that will allow me to go to the same cell in a sheet, but then sum the total column data that is at the top of the sheet.
    My problem is that we add a new line every month to the data so the sum of data needs to capture not only what was previously there but the new cell that has been added.

    So far i have put this together:
    Cells.Find(What:="CellName", After:=ActiveCell, LookIn:=xlFormulas _
    , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(2, 5).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-121]C[-4]:R[-113]C[-4])"
    This works as long as you don't add data, but if I add data, then it doesn't capture the new line put in above R[-121] which is now R[-122].

    I have worked out how to capture the whole data within the column that I need, but don't know how to put the two together.
    Range("c4", Range("c4").End(xlDown)).Select

    Thanks again for your help.
    Strybes
     
  2. turbodante

    turbodante

    Joined:
    Dec 19, 2008
    Messages:
    744
    try this...

    Code:
    Sub summy()
    Dim eRow As Long
    eRow = Range("c4").End(xlDown).Row
    Cells.Find(What:="CellName").Offset(2, 5) = "=sum(c4:c" & eRow & ")"
    End Sub
    
     
  3. Strybes

    Strybes Thread Starter

    Joined:
    Sep 7, 2007
    Messages:
    67
    Thank you so much turbodante. Does exactly what i was trying to do.

    Thanks
    Strybes
     
As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/913523

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice