Changing VBA code to function

[Vispon]

Hi there

I do apologise if this is a rather noobie question but I've been given a piece of VBA code and I'm completely failing to work out how to adapt it to my needs.

The code is designed to work out the total cost to the customer for buying various quantities of a single product. The price scale works that the first 5 are x price the next 5 are at a slightly cheaper rate, next 5 cheaper again etc.

So as an example the per unit price for 5 units is 30, the next 5 (per unit) are 24, next 5 are 20. So the purchase of 12 units would cost 5x30 + 5x24 + 2x20 (310)

The vb code I have is:

Please Login or Register  to view this content.

So this does the correct calculation but is hard coded to check the value from L10 and place the result in M10. This formula is going to be used on multiple sheets so each sheet will be checking a different cell and writing to a different cell.

From what I was reading it sounded like using this as a function might be the solution but I'm not at all sure if that's right or indeed possible. Any help would be very appreciated.

[Izandol]

Can you provide a sample workbook for review? A function should accept all the data as inputs and not use references such as Cells(r.Row, "M").Value

[Vispon]

Apologies, example spreadsheet attached.

P Example.xlsm

So in the example the calculations work on the rates tab but I'm struggling to get it to work on the customer a and customer b tabs.

[:) Sixthsense :)]

Just convert the L3 to M7 data to real numbers

In L10 Cell – Input cell
25

In M10 Cell

=SUMPRODUCT((M3:M7<=L10)*((M3:M7-L3:L7)+1),N3:N7)+IFERROR(LOOKUP(L10,L3:L7,N3:N7),0)*IFERROR(L10-LOOKUP(L10,M3:M7),0)+IF(L10<MIN(M3:M7),IFERROR(L10*LOOKUP(L10,L3:L7,N3:N7),0),0)

Refer the attached file for details…

[Vispon]

Ah the excel cycle, you spend hours working on an unnecessarily complicated way of solving a problem and then someone shows a much simpler (and better) solution

Works perfectly, thanks sixth sense

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback

[:) Sixthsense :)]

Little bit fine tuned version of the formula...

Since you are already having the named range for the Price Table just use the below formula in M10 Cell

=SUMPRODUCT((INDEX(handsetcharges,,2)<=L10)*((INDEX(handsetcharges,,2)-L3:L7)+1),INDEX(handsetcharges,,3))+IFERROR(LOOKUP(L10,INDEX(handsetcharges,,1),N3:N7),0)*IFERROR(L10-LOOKUP(L10,INDEX(handsetcharges,,2)),0)+IF(L10<MIN(INDEX(handsetcharges,,2)),IFERROR(L10*LOOKUP(L10,INDEX(handsetcharges,,1),INDEX(handsetcharges,,3)),0),0)

Used the range in Index for your easy editing purpose...

Use the below formula in M10 Cell

=SUMPRODUCT((INDEX($L$3:$N$7,,2)<=L10)*((INDEX($L$3:$N$7,,2)-INDEX($L$3:$N$7,,1))+1),INDEX($L$3:$N$7,,3))+IFERROR(LOOKUP(L10,INDEX($L$3:$N$7,,1),INDEX($L$3:$N$7,,3)),0)*IFERROR(L10-LOOKUP(L10,INDEX($L$3:$N$7,,2)),0)+IF(L10<MIN(INDEX($L$3:$N$7,,2)),IFERROR(L10*LOOKUP(L10,INDEX($L$3:$N$7,,1),INDEX($L$3:$N$7,,3)),0),0)