Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Cambridgeshire, USA
    Posts
    109
    Thanks
    0
    Thanked 0 Times in 0 Posts

    subtracting highlighted cells (2003)

    Is there a way to subtract highlighted fields from a formula. For example, the formula is adding cells a5 to a30, but I need to subtract some cells within that range that are highlighted. Any help would be great. Thanks.

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: subtracting highlighted cells (2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> There

    I think you need to write some VBA for this kind of work.

    1) You have the range of cells to add.
    2) You know what cells are selected
    3) Do the math.

    I will send you a sample code in a minute

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: subtracting highlighted cells (2003)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> JoseLuis

    Try and clean up this code and see what you can make out of it. I wrote this very fast while having lunch so it is not too pretty.

    But it can be a start.

    Wassim

    Option Explicit

    Sub AddWithoutHighlighting()
    Dim rRangeToAdd As Range '/This is the range to add.
    Dim rRangeHighlighted As Range '/This is the range Highlighted.
    Dim vCell As Variant '/This is the cell mover.
    Dim sRangeHighlighted As String '/String to hold the addresses of the selected cells.
    ' Dim iCounter As Integer '/A simple Counter.
    Dim lSum As Long '/This is the Sum

    Set rRangeToAdd = ActiveSheet.Range("A5:A30")
    Set rRangeHighlighted = Selection.Cells '/This is a ref. to the current selection

    For Each vCell In rRangeHighlighted.Cells
    sRangeHighlighted = sRangeHighlighted & ", " & vCell.Address
    Next vCell

    lSum = 0

    For Each vCell In rRangeToAdd.Cells
    If InStr(1, sRangeHighlighted, vCell.Address) > 0 Then '/Cell is highlighted
    '/Skip Addin this cell
    Else
    lSum = lSum + vCell.Value
    End If
    Next vCell

    MsgBox lSum
    End Sub
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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