Calculating between 2 worksheets

[vhrame]

Hello,

I have NEVER done calculations using VBA and am pretty new to this world but always eager to learn, and I am need of some help. I have a worksheet called "Wheels Trans" which has a table of data and it also has 3 columns called "DAYS CHARGED", WEEKS CHARGED", "MONTHS CHARGED".



DAYS CHARGED
WEEKS CHARGED
MONTHS CHARGED
Comm Amt to Charge




0
3
0




0
0
1


1
0
0





I have a file in another tab called "Tier"



Tier
Millions
Daily
Monthly


I
0-$15
9.08
230.00


II
$15+
15.14
232.50




In the "Comm Amt to Charge" column, I would like the calculation as follows: Looking at row 2 of table 1, MONTH CHARGED is 1) so i would like to have the calculation (1*230.00). The DAYS CHARGED with 1 would be (1*9.08) and the CHARGED WEEKS with 3 would be (3*7 = 21 which is converted to days) and then multiplied by 9.08. I hope this makes sense. I would like if it would loop until the last row. I want it to ignore the 0s and calculate all that have values.

Any help is appreciated.

[SamT]

Where is the value that determines which Tier to use Stored?
Why don't you have a Weekly column in Tier table?

What to do if Days Charged => 7? Weeks Charged => 4?

[vhrame]

Right now I am just using Tier 1, i apologize i should have just put in Tier 1. I dont maintain this table and so this table will not have a weekly rate instead they want the number of weeks in the weekly charged column to be multiplied by 7 (number of days in the week) and then multiply that by 9.08. The same goes for the number of days, whatever number is in the days charged column will be multiplied by the daily rate. I hope this makes sense.

Regards

[SamT]

"Days", Weeks", and "Months" are bad choices for Function names, So I will Use "DailyRateTimes", WeeklyRateTimes" and MonthlyRateTimes" As Functions in a Module. You will need to verify all range addresses yourself


Public Function DailyRateTimes(Target As Range) As Currency
Dim DailyRate As Currency
DailyRate = Sheets("Tier").Range("C2")

DailyRateTimes = DailyRate * Target
End Function


Public Function WeeklyRateTimes(Target As Range) As Currency
Dim WeeklyRate As Currency
WeeklyRate = Sheets("Tier").Range("C2") * 7

WeeklyRateTimes = WeeklyRate * Target
End Function


Public Function MonthlyRateTimes(Target As Range) As Currency
Dim MonthlyRate As Currency
MonthlyRate = Sheets("Tier").Range("D2")

MonthlyRateTimes = MonthlyRate * Target
End Function

Copy those three Functions in to a Module in the workbook. The Formula in D2, the first cell under the "Comm Amt to Charge' cell will be

=SUM(DailyRateTimes(A2),WeeklyRateTimes(B2),MonthlyRateTimes(C2))

Drag that formula down as far as needed

[vhrame]

Sam, this is awesome! And it worked! :-). Thank you so much.

So I have another question. What if I wanted a macro to enter the value in the comm amt charged column, without me having to insert the "=SUM(DailyRateTimes(A2),WeeklyRateTimes(B2),MonthlyRateTimes(C2))" formula manually in that cell. Is that doable?

[SamT]

Add this Function below the other three


Public Function CommAmt(Target as Range)As Currency
CommAmt = DailyRateTimes(Target.Cells(1)) + WeeklyRateTimes(Target.Cells(2)) + MonthlyRateTimes)Target.Cells(3))
End function

The New Formula in the Comm Amt Charged Cell is

=CommAmt(A2:C2)

The reason for this extended style is If/When the PTB start using Tier 2 etc, you can modify the three RateTimes functions without effecting the formula or the CommAmt Function.

Note: If you use the four Functions Style, you can reScope the three RateTimes Functions to Private. This will remove them from the Functions list in the Excel Formula Bar

[SamT]

The other Option, not using on sheet formulas, is to use the Worksheet_Change Event Sub;
The Worksheet code will be




Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row >= 2 Then
If Not Intersect(Target, Columns("A")) Is Nothing Then DailyRateTimes Target
If Not Intersect(Target, Columns("B")) Is Nothing Then WeeklyRateTimes Target
If Not Intersect(Target, Columns("C")) Is Nothing Then MonthlyRateTimes Target
End If
End Sub

Private Sub DailyRateTimes(Target As Range)
Dim DailyRate As Currency
DailyRate = Sheets("Tier").Range("C2")

With Cells(Target.Row, "D")
.Value = .Value + (DailyRate * Target)
End With
End Sub

Private Sub WeeklyRateTimes(Target As Range)
Dim WeeklyRate As Currency
WeeklyRate = Sheets("Tier").Range("C2") * 7

With Cells(Target.Row, "D")
.Value = .Value + (WeeklyRate * Target)
End With
End Sub

Private Sub MonthlyRateTimes(Target As Range)
Dim MonthlyRate As Currency
MonthlyRate = Sheets("Tier").Range("D2")

With Cells(Target.Row, "D")
.Value = .Value + (MonthlyRate * Target)
End With
End Sub

This will only run when you change a value in Columns A:C. You should now delete all Functions in the Standard Module.

The only caveat is that if you change a previous value, other than from zero, you must manually correct the Comm Amount.

[vhrame]

SamT, thank you so much for all your help. Everything worked the way I needed it to work. Truly appreciate all your help! :thumb