+ Reply to Thread
Results 1 to 2 of 2

Help with formula

  1. #1
    Registered User
    Join Date
    08-24-2004
    Posts
    14

    Help with formula

    Hello,

    I have a report that lists sales by portfolio. The sales are listed in columns E and F. I would like a macro that sums up the sales in these columns. I'm having trouble because the number of porfolios change from week to week. I need something that will find the last cell in each column and add the formula in the cell below it.

    Thanks in advance.

  2. #2
    Registered User
    Join Date
    04-21-2005
    Posts
    46
    You can't create a formula that will insert another formula into another cell, but you can create a button or similar VBA object to accomplish the result. To do so, insert this code:

    Private Sub CommandButton1_Click()
    Dim etop As Long
    Dim ebot As Long
    Dim ftop As Long
    Dim fbot As Long
    etop = 0
    ebot = 0
    ftop = 0
    fbot = 0
    Do
    etop = etop + 1
    If Cells(etop, 5).Value <> "" Then GoTo FoundETop
    Loop While etop < 65536
    GoTo ENoData
    FoundETop:
    ebot = etop
    Do
    ebot = ebot + 1
    If Cells(ebot, 5).Value = "" Then GoTo FoundEBot
    Loop While ebot < 65536
    FoundEBot:
    Cells(ebot, 5).Value = "=sum(E" & etop & ":E" & ebot - 1 & ")"
    Cells(ebot, 5).Font.Bold = True

    ENoData:
    Do
    ftop = ftop + 1
    If Cells(ftop, 6).Value <> "" Then GoTo FoundFTop
    Loop While ftop < 65536
    GoTo FNoData

    FoundFTop:
    fbot = ftop
    Do
    fbot = fbot + 1
    If Cells(fbot, 6).Value = "" Then GoTo FoundFBot
    Loop While fbot < 65536
    FoundFBot:
    Cells(fbot, 6).Value = "=sum(F" & ftop & ":F" & fbot - 1 & ")"
    Cells(fbot, 6).Font.Bold = True

    FNoData:
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1