Separating single cell value into different cells

[manfredleong]

Hi all, I am quite a VBA newbie and I got a tricky scenario (at least to me) to solve.

I am writing a code for automation for my company and I need to separate a single cell value into different cells. Let me explain.

For example in a certain column, the values of cells ranges from 1 is 130,000. I need to separate anything above 50,000 into 2 or more cells, each cell containing not more than 50,000. So if the cell value is 130,000, I need to separate into 50,000 in cell 1, 50,000 in cell 2 and 30,000 in cell 3.

Can it be done?

Thank you in advance

 

[TonyUK72]

If you value is in A2, then B2, C2 and D2 would have the following formulas.

=IF(A2="","",IF(A2>50000,50000,A2))

=IF(B2="","",IF(A2-B2>50000,50000,A2-B2))

=IF(C2="","",IF(A2-SUM(B2:C2)>50000,50000,A2-SUM(B2:C2)))

You can use as many formulas as you need to accommodate your largest total.

 

[MrJoosten]

Hello,

May I ask why you don't use a formula behind the column with values?

If your values are in column A it would be:

Column B: =IF(A1<=50000;A1;IF(A1>50000;50000))
Column C: =IF((A1-B1)<50000;A1-B1;IF((A1-B1)>50000;50000))
Column D: =IF((A1-C1-B1)<50000;A1-C1-B1;IF((A1-C1-B1)>50000;50000))

 

[S_Wish]

A10=number
B10=IF(A10>50000;50000;A10)
C10 and further =IF($A$10-SUM($B10:B10)>50000;50000;$A$10-SUM($B10:B10))

 

[Akuini]

Well, if you want vba, try this:
I assume the original value (in col A in the below code) is also changed.

Sub a1015143b()
Dim i As Long, j As Long, x As Long, y As Long
Dim r As Range

'Change column A to suit
For Each r In Range("A1", Cells(Rows.count, "A").End(xlUp))

    If r > 50000 Then
        x = r \ 50000
        y = r Mod 50000
            For j = 0 To x - 1
            r.Offset(0, j) = 50000
            Next
            If y <> 0 Then r.Offset(0, x) = y
    End If
Next

End Sub