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

Thread: Using macro to divide across cells

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I use the below code to divide selected cells across 12 visible columns. It works great, except that it puts values in and people viewing the worksheet like to see the formulas. Is there anyway for a formula to show in each of the 12 cells.

    Thanks

    Sub DivideItUp()
    Dim c%, area As Range, colLetter$, avg#, Cell As Range, rng As Range, col%, x%, dest As Range
    c = 4 'Column number in which cells are to be selected (change as required)
    For Each area In Selection.Areas
    If area.Columns.Count > 1 Or area.Column <> c Then
    With ActiveSheet.Columns(c)
    colLetter = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
    End With
    MsgBox "You must make a selection only in Column " & colLetter & " before running this macro."
    Exit Sub
    End If
    Next
    Set rng = Intersect(ActiveSheet.UsedRange, Selection)
    Application.ScreenUpdating = False
    For Each Cell In rng
    avg = Application.Sum(Cell) / 12
    col = c + 1
    x = 0
    Do
    Set dest = Cells(Cell.Row, col)
    If dest.EntireColumn.Hidden = False Then
    dest.Value = avg
    col = col + 1
    x = x + 1
    Else: col = col + 1
    End If
    Loop While x < 12
    dest.Offset(0, 1).Formula = "=sum(" & Cells(Cell.Row, c + 1).Address & ":" & dest.Address & ")"
    Next
    End Sub

  2. #2
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ActiveWindow.DisplayFormulas = True

    Will turn on Formulas for the worksheet.

    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm sorry, I didn't explain very well. This code will turn everything into a formula and that will be the value in the cell. What I actually want is to have the result of the formula in the cell, but when the user clicks on a given cell, they can see the formula in the formula bar.

    Is this possible?

  4. #4
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What formula do you want displayed in the cells...Application.Sum(Cell) / 12 ?

  5. #5
    MrExcel MVP Al Chara's Avatar
    Join Date
    Feb 2002
    Location
    Newark, Delaware
    Posts
    1,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    If it is, then try the following:

    Dim c%, area As Range, colLetter$, avg#, Cell As Range, rng As Range, col%, x%, dest As Range
    c = 4 'Column number in which cells are to be selected (change as required)
    For Each area In Selection.Areas
    If area.Columns.Count > 1 Or area.Column <> c Then
    With ActiveSheet.Columns(c)
    colLetter = Left(.Address(False, False), InStr(.Address(False, False), ":") - 1)
    End With
    MsgBox "You must make a selection only in Column " & colLetter & " before running this macro."
    Exit Sub
    End If
    Next
    Set rng = Intersect(ActiveSheet.UsedRange, Selection)
    Application.ScreenUpdating = False
    For Each Cell In rng
    col = c + 1
    x = 0
    Do
    Set dest = Cells(Cell.Row, col)
    If dest.EntireColumn.Hidden = False Then
    dest.Formula = "=sum(" & Cell & ")/12"
    col = col + 1
    x = x + 1
    Else: col = col + 1
    End If
    Loop While x < 12
    dest.Offset(0, 1).Formula = "=sum(" & Cells(Cell.Row, c + 1).Address & ":" & dest.Address & ")"
    Next
    Best regards,
    Allan Chara
    http://www.mrspreadsheets.com

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Al!

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
  •