Getting Item Rate from worksheet and calculating total item rates

**skhari** · 07-14-2015, 09:17 AM

Dear Experts

Please help me getting Item Rate from worksheet to userform.

I have a form which help us to getting details of daily material consumption, now I have to add RATE value for each material and calculate according to the Qty or Nos.
( Private Sub Rate1_Change() ) and finally total material cost to be added in DailyConsumption Worksheet before the end line of the particular Date and Job Code

Item Rates are referred from RawMaterialList Worksheet

Kindly refer the attachments

Please help me on this task

- SK

**Arkadi** · 07-15-2015, 08:19 AM

Skhari,

On the form, the box for Total Material Cost/Rate... should that be the sum of "Rate" only? Or Rate x Units?

**skhari** · 07-15-2015, 10:52 AM

Arkadi, Thanks for attending my post.

Your question is right.
Scenario 1
If the user selects Units in Nos. then
Rate = Base Rate x Nos.

Scenario 2
If the user selects Units in kg.,sq.ft, sq.m etc other than Nos then
Rate = Base Rate x Qty.

therefore Total Material Cost/Rate = sum of rate(s)

If possible please add one more text box for Base Rate on each row (in Material Consumed area).
I got this idea of adding base rate (text box) because you shooted the right question

thanks for that.

Base Rate = RawMaterialList!B2:B

Hope I have explained what I have in my mind.

Thank you so much for your time.

SK

**Arkadi** · 07-15-2015, 03:23 PM

Skhari, have not had a lot of time, and had to rewrite some of the code for the form, making progress, but it may take another day or so to post something back.

**Arkadi** · 07-16-2015, 11:05 AM

Skhari, here is what I have so far... I'll leave it to you to sort out the pasting when submit is pressed, I have taken care of the form getting base rate, rate, and total rate.

**skhari** · 07-17-2015, 04:17 PM

Arkadi, Thanks a lot for your great help. so far so gooood.

Well I tried and could not fix the following issues.
1. In Form : Total Material Cost shows only the first row rate value and it is not calculating the rates of new rows.
2. After Submitting: The last row rates are going below the cell line.
3. Need to add a line before the End row with details of Date(A), Job Code(B) Total Cost(L) - Ref. highlighted in blue

Attached picture for your reference..

Once again Thank you so much for your big help. Please take your time. and sorry troubling you again.

- SK