Spread values over 12 columns

Hi

Ok so you have values in A1:A16 and you want to divide them by twelve and put the values in 12 columns right? You want the values all the same and dont care about remainders?

This should work.

Sub DiveeItUp()
Dim MyPartialValue

for x = 1 to 16
MyPartialValue = Range("A" & x).value/12
range("B" & x).value = MyPartialValue
range("C" & x).value = MyPartialValue
range("D" & x).value = MyPartialValue
range("E" & x).value = MyPartialValue
range("F" & x).value = MyPartialValue
range("G" & x).value = MyPartialValue
range("H" & x).value = MyPartialValue
range("I" & x).value = MyPartialValue
range("J" & x).value = MyPartialValue
range("K" & x).value = MyPartialValue
range("L" & x).value = MyPartialValue
range("M" & x).value = MyPartialValue

next x
End Sub

HTH
Jacob

area and spread it across the first 12 VISIBLE columns. By spread, I mean divide by twelve.

Or perhaps ......

Sub DivideItUp()
Dim avg#, col%, x%, cell As Range
avg = Application.Sum(Selection) / 12
col = 2
x = 0
Do Until x = 12
Set cell = Cells(1, col)
If cell.EntireColumn.Hidden = False Then
cell.Value = avg
col = col + 1
x = x + 1
Else: col = col + 1
End If
Loop
Cells(1, col).Formula = "=sum($B$1:" & Cells(1, col - 1).Address & ")"
End Sub

Divide each by twelve, not the sum...

Hello - I thought this would work because it appears the selected cells will not have to be hardwired, and I definitely want to avoid doing that since it would defeat the purpose of writing the macro.

However, I want each value to divide by 12 individually. I also want it to do it in the same row in which the value being divided is located.

Any ideas? Thanks :-) Gaby

Re: Divide each by twelve, not the sum...

Sub DivideItUp()
Dim area As Range, avg#, col%, x%, cell As Range, dest As Range
For Each area In Selection.Areas
If area.Columns.Count > 1 Or area.Column <> 1 Then
MsgBox "You must make a selection only in Column A before running this macro."
Exit Sub
End If
Next
For Each cell In Selection
avg = Application.Sum(cell) / 12
col = 2
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
Cells(cell.Row, col).Formula = "=sum($B$" & cell.Row & ":" & Cells(cell.Row, col - 1).Address & ")"
Next
End Sub

Re: Divide each by twelve, not the sum...

Brilliant, Thanks :-). One followup, what if the selected area lies in column B? Thanks :-)

: Hello - I thought this would work because it appears the selected cells will not have to be hardwired, and I definitely want to avoid doing that since it would defeat the purpose of writing the macro. : However, I want each value to divide by 12 individually. I also want it to do it in the same row in which the value being divided is located. :-) Gaby

Question ....

Do you mean that the selected area(s) can be in column B OR in column A ?
Or do you mean in column B INSTEAD OF column A ?

Brilliant, Thanks :-). One followup, what if the selected area lies in column B? Thanks :-)

: Dim area As Range, avg#, col%, x%, cell As Range, dest As Range : For Each area In Selection.Areas : If area.Columns.Count > 1 Or area.Column <> 1 Then : MsgBox "You must make a selection only in Column A before running this macro." : Exit Sub : Next : For Each cell In Selection : avg = Application.Sum(cell) / 12 : Do : Set dest = Cells(cell.Row, col) : If dest.EntireColumn.Hidden = False Then : dest.Value = avg While x < 12 : Cells(cell.Row, col).Formula = "=sum($B$" & cell.Row & ":" & Cells(cell.Row, col - 1).Address & ")" : Next :

Re: Question ....

I would prefer it not matter what column I am in, but if it must, then let's make it B INSTEAD of A.
Thanks:-) Gaby Do you mean that the selected area(s) can be in column B OR in column A ? Or do you mean in column B INSTEAD OF column A ? : Brilliant, Thanks :-). One followup, what if the selected area lies in column B? Thanks :-)

Re: Question ....

Still not entirely sure how much flexibility you need, so the following will allow selection in one column only.
The code posted allows for selection in column B only, but it is merely a matter of changing the line indicated if a different column is to be the selection column.

Sub DivideItUp()
Dim c%, area As Range, colLetter$, avg#, cell As Range, rng As Range, col%, x%, dest As Range

c = 2 '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

I would prefer it not matter what column I am in, but if it must, then let's make it B INSTEAD of A. Thanks:-) Gaby : Do you mean that the selected area(s) can be in column B OR in column A ? : Or do you mean in column B INSTEAD OF column A ? :

This will do nicely. Thanks soooo much!!!!

Thanks :-)