rounding problem in vba

[TheDude111]

hey guys

Im using excel 2011,

basically what Im doing is taking a product quantity and dividing it among customer based on a percentage.

e.g. divide 10 apples between 2 customers A and B with A getting 70% and B getting 30 % etc.

However some products will only get sold in groups of 4,6,8 etc.

In my formula this is what is in column AP


I was using application.round which works for the majority of cases in my formula.

Code:

  <style>.alt2 font { font: 11px monospace ! important; color: rgb(51, 51, 51) ! important; }</style>  
[FONT=Arial][SIZE=2][COLOR=#000000]While (Range("A" & loopCounter4) <> "")

        If (Range("E" & loopCounter4) = "A") And (Range("G" & loopCounter4) = "1") Then
              Range("AC" & loopCounter4) = Application.Round(((Range("AB" & loopCounter4) / Range("AP" & loopCounter4))) * 0.3, 0) * Range("AP" & loopCounter4)
            
        ElseIf (Range("E" & loopCounter4) = "B") And (Range("G" & loopCounter4) = "1") Then
                Range("AC" & loopCounter4) = Application.Round(((Range("AB" & loopCounter4) / Range("AP" & loopCounter4))) * 0.7, 0) * Range("AP" & loopCounter4)[/COLOR][/SIZE][/FONT]

There are cases however when i end up with a ".5" for each and both are rounded up. Which leaves me with more than the original figure.

I tried using a rounding function

Code:

  <table class="tborder" id="post2570431" align="center" border="0" cellpadding="6" cellspacing="0" width="100%"><tbody><tr><td class="alt1" id="td_post_2570431"><style>.alt2 font { font: 11px monospace ! important; color: rgb(51, 51, 51) ! important; }</style>  
[FONT=Arial][SIZE=2][COLOR=#000000]Function RoundSpecial(pValue As Double) As Double

Dim LWhole As Long

Dim LFraction As Double

'Retrieve the interger
LWhole = Int(pValue)

'Retrieve the fraction
LFraction = pValue - LWhole

If LFraction < 0.5 Then
    CustomRound = LWhole
Else
    CustomRound = LWhole + 0.5

End If

End Function[/COLOR][/SIZE][/FONT]

Any help or ideas would be appreciated.</td></tr></tbody></table>

 

[GlennUK]

Can you give an example of your data layout?

 

[TheDude111]

Can you give an example of your data layout?

It is something like the above,I need the splits to go into the green cells

 

[GlennUK]

... still tweaking formula

 

[GlennUK]

For that particular layout ( and making some assumptions about your data ), this does what you want:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Customer Number	Customer	City Number	City Name	Available	*	Pack	*
2	1	Ireland	123	*	200	*	12	*
3	1	Ireland	123	Cork A	*	60	*	30%
4	1	Ireland	123	Cork B	*	140	*	70%
5	2	UK	321	*	1000	*	8	*
6	2	UK	321	London A	*	152	*	15%
7	2	UK	321	London B	*	848	*	85%
Sheet2

That gives the split where the first amount is rounded to fit into a whole number of packs.

Can you tell me what the actual numbers should be for the output?

 

[GlennUK]

Actually, you wanted number of packs, didn't you, so this may be better:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Customer Number	Customer	City Number	City Name	Available		Pack
2	1	Ireland	123		200		12
3	1	Ireland	123	Cork A		5		30%
4	1	Ireland	123	Cork B		11		70%
5	2	UK	321		1000		8
6	2	UK	321	London A		19		15%
7	2	UK	321	London B		106		85%
Sheet2

 

[TheDude111]

hey Glen

thanks for the above, however iv decided to go a cleaner route using WorksheetFunction.RoundDown


However i have one last question.

Say i have a range of numbers
in A,B and C

So A1 is blank
A2 =42
A3 = 42

B1 = Blank
B2 = Blank
B3 = 29

C1 = blank
C2 = Blank
C3 = 13

I want to take the value in C3 and move into B2, for the entire range

I have tried using a combination of if statements and ranges but cant quite get it to work