Divide number beetwen cells

[Pontos]

Hi.

I want to equaly divide number which is in cell E4 on range (A1:B80). How can I do that?

 

[GlennUK]

Do you mean that the area A1:B80 is currently blank, and you want a formula in those cells to get 1/160th of E4 in each cell?

 

[Pontos]

Yes, exactly. But ranges can be different.

 

[syntaxed]

Try this:

Function Distribute(SourceCell As Range)
    Dim V() As Variant
    myrows = Selection.Rows.Count
    mycols = Selection.Columns.Count
    ReDim V(1 To myrows, 1 To mycols)
    For R = 1 To myrows
        For C = 1 To mycols
            V(R, C) = SourceCell.Value / Selection.Count
        Next
    Next
    Distribute = V
End Function

Select the entire range you want, then type =Distribute(E4), and press Ctrl+Shift+Enter

 

[syntaxed]

The function I gave above will not remember the selection and will change its results whenever the selection changes.

This function fixes that problem:

Function Distribute(SourceCell As Range, OutputRange As Range)
    Dim V() As Variant
    myrows = OutputRange.Rows.Count
    mycols = OutputRange.Columns.Count
    ReDim V(1 To myrows, 1 To mycols)
    For R = 1 To myrows
        For C = 1 To mycols
            V(R, C) = SourceCell.Value / OutputRange.Count
        Next
    Next
    Distribute = V
End Function

You may use it like this: Select the entire range you want (e.g. A1:B80), then type =Distribute(E4, A1:B80), and press Ctrl+Shift+Enter

 

[Pontos]

Thanks - it's working fine.