Solved: How to: Function > Calculate sumof and divide

[WYLIE]

This is my 2nd week of using VBA and have come across a stumbling block.

The issue is I have used functions to do my calculations on the data captured in the user form but how do I calculate the sum total of calculations previously and divide that total by 60. (its in relation to converting minutes into hours)

Function WeightingCalculator(Process As String, Quantity As Long)
Select Case Process
Case "Archiving"
WeightingCalculator = Quantity * 30
Case "CPI"
WeightingCalculator = Quantity * 15
Case "Faxes"
WeightingCalculator = Quantity * 3
Case "File Makeup"
WeightingCalculator = Quantity * 4
Case "Incoming Post"
WeightingCalculator = Quantity * 120
Case "Kofax Scanning"
WeightingCalculator = Quantity * 2
Case "Mailbox monitoring"
WeightingCalculator = Quantity * 15
Case "Mental Health"
WeightingCalculator = Quantity * 1
Case "Total"
…………………………………… ?????
End Select
End Function


Any help would be greatly appreciated and thank you for your assistance to the newbie :)

[Bob Phillips]

What are you trying to SUM?

[WYLIE]

Hi, I have form that captures the quantity completed by my work colleagues for each work task, "Archiving”, “CPI”, “Faxes” etc and using the function method I have multiplied each individual task by the weight barring (time it takes to completed) of each task.

What I need is to calculate the total (sum) of all tasks when they have been multiplied and convert this into “time” using VBA.

I hope this makes it a little bit clearer?

[Bob Phillips]

Have you tried the SUMIF function?

Maybe your workbook will help.

[Paul_Hossler]

If I'm reading this correctly, your user defined function does not have the information needed to actually total


Case "Total"
…………………………………… ?????


Couldn't you put a =SUM (.....) / 60 worksheet formula where you need it?

Paul

[WYLIE]

If I'm reading this correctly, your user defined function does not have the information needed to actually total


Paul

Hi Paul

Each task which is selected by the user from a drop down menu has a specified time limited, i.e. it takes 30 minutes to do the Archiving task. with the case function

Select Case Process
Case "Archiving"
WeightingCalculator = Quantity * 30

This is captured and the calculation is calculated and updated in my main workbook.


Couldn't you put a =SUM (.....) / 60 worksheet formula where you need it?

Paul

Currently I'm doing this, I've pre- populated main workbook with this formula =SUM(D6:T6)/60 and to convert into time I’ve done =TEXT(W6/24,"[h]:mm") in cell beside the calculation

But the aim is not to do this, have this calculation in VBA which I don’t understand how to do and populate the "Total" cell only if the that row has data in it and convert the total in time (hh:mm) 2:45 which is easier to read that 2.75


That is my dilemma :banghead:


***
Please find attached the excel workbooks tracker and warekhouse
Hopefully this will help to understand my problem. I have deleted the piviot table and chart due to file size

To access the warehouse workbook, PW is "Test"

***

Your help is very much appreciated

[WYLIE]

Sheets("Raw Data").Cells(nextemptyrow, processAcolumn).Value = Format(WeightingCalculator(processA, qtya), "hh:mm")


For anyone's informtion on how to convert into time. I was unable to do the TOTAL

[WYLIE]

Sheets("Raw Data").Cells(nextemptyrow, processAcolumn).Value = Format(WeightingCalculator(processA, qtya), "hh:mm")


For anyone's informtion on how to convert into time. I was unable to do the TOTAL of all convert time, unless i use =SUM.

Thanks

[Bob Phillips]

Is there still an outstanding question here, or is it fully solved?

[WYLIE]

Hi I have been able to do the calculation above - Matter is solved