# Thread: Calculating between 2 worksheets

1. ## Calculating between 2 worksheets

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.  Reply With Quote

2. 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?  Reply With Quote

3. 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  Reply With Quote

4. "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  Reply With Quote

5. 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?  Reply With Quote

6. 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  Reply With Quote

7. 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.  Reply With Quote

8. SamT, thank you so much for all your help. Everything worked the way I needed it to work. Truly appreciate all your help!   Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•