Back into amount given specific lots/amounts

[emoandy05]

Hey,

Appreciate the help in advance.

I have an input value, say 515,000,000. Given that value, I would like to back into that value by inputting a lot size, say 50,000,000, that add up to 515,000,000, with the remaining odd peice being 15,000,000. I would like the resutls to generate as shown below (row after row), given the two inputs.

Any help on a macro for this? Not sure where to begin.

For example:

My Inputs:
Total size = 515,000,000
Lot size = 50,000,000

What I would like to have as the output in this scenario:
50,000,000
50,000,000
50,000,000
50,000,000
50,000,000
50,000,000
50,000,000
50,000,000
50,000,000
50,000,000
15,000,000

Thank you.

 

[MickG]

Try this:-
NB:- "B2" = 550,000,000 and "B3" = 5,0000

[COLOR="Navy"]Sub[/COLOR] MG24Nov26
[COLOR="Navy"]Dim[/COLOR] Tot [COLOR="Navy"]As[/COLOR] Double, Lot [COLOR="Navy"]As[/COLOR] Double, Num [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Tot = Range("B2").Value
Lot = Range("B3")
Num = Tot / Lot
[COLOR="Navy"]For[/COLOR] n = 1 To Int(Num)
    Range("A5").Offset(n).Value = Lot
[COLOR="Navy"]Next[/COLOR] n
    Range("A5").Offset(Int(Num) + 1).Value = Tot Mod Lot
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[emoandy05]

Try this:-
NB:- "B2" = 550,000,000 and "B3" = 5,0000

[COLOR=navy]Sub[/COLOR] MG24Nov26
[COLOR=navy]Dim[/COLOR] Tot [COLOR=navy]As[/COLOR] Double, Lot [COLOR=navy]As[/COLOR] Double, Num [COLOR=navy]As[/COLOR] [COLOR=navy]Integer,[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
Tot = Range("B2").Value
Lot = Range("B3")
Num = Tot / Lot
[COLOR=navy]For[/COLOR] n = 1 To Int(Num)
    Range("A5").Offset(n).Value = Lot
[COLOR=navy]Next[/COLOR] n
    Range("A5").Offset(Int(Num) + 1).Value = Tot Mod Lot
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Youre the man! Thank you sir.

 

[emoandy05]

So I found that is the "tot" is less than the "lot", say 48,000,000, the macro returns a 50,000,000 and a 48,000,000.

Also, it seems to add and extra 50,000,000 if the "tot" is on the other side of a 25,000,000 increment. For example, 128,000,000 returns 50,000,000, 50,000,000, 50,000,000, and 28,000,000.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG25Nov48
[COLOR="Navy"]Dim[/COLOR] Tot [COLOR="Navy"]As[/COLOR] Double, Lot [COLOR="Navy"]As[/COLOR] Double, Num [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Tot = Range("B2").Value
Lot = Range("B3")
Num = Tot / Lot
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A5"), Range("A" & Rows.Count).End(xlUp))
Rng.ClearContents
[COLOR="Navy"]If[/COLOR] Tot <= Lot [COLOR="Navy"]Then[/COLOR]
 Range("A5").Value = Tot
[COLOR="Navy"]Else[/COLOR]
[COLOR="Navy"]For[/COLOR] n = 1 To Int(Num)
    Range("A4").Offset(n).Value = Lot
[COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]If[/COLOR] Not Num = Int(Num) [COLOR="Navy"]Then[/COLOR] Range("A4").Offset(Int(Num) + 1).Value = Tot Mod Lot
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]If[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick