Consulting

Results 1 to 8 of 8

Thread: Calculating between 2 worksheets

  1. #1
    VBAX Regular
    Joined
    Sep 2019
    Posts
    23
    Location

    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.

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,188
    Location
    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?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Sep 2019
    Posts
    23
    Location
    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

  4. #4
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,188
    Location
    "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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  5. #5
    VBAX Regular
    Joined
    Sep 2019
    Posts
    23
    Location
    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?

  6. #6
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,188
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  7. #7
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,188
    Location
    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.
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  8. #8
    VBAX Regular
    Joined
    Sep 2019
    Posts
    23
    Location
    SamT, thank you so much for all your help. Everything worked the way I needed it to work. Truly appreciate all your help!

Posting Permissions

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