A challenge!

J

Joeslost

New Member
Joined
Jun 1, 2016
Messages
20
Hello all!

My name is Joe and I have a complex problem to solve that may prove a nice challenge for some of you excelperts!

I have a document that I use to generate contracts by importing information from another program. But I would like it to do more.

More on this Document Here: http://www.mrexcel.com/forum/excel-...ng-between-two-excel-sheets-out-setnames.html
And Here: http://www.mrexcel.com/forum/excel-questions/949429-otherworkbook-function.html

As of now there is a charge that applys in most situation in different ways depending on the customer and items delivered to them that I need to manually calculate.

Here’s as close to a plain English explanation of whats going on that I can muster.
This document has information imported into it so it’s different every time it gets ran. It is a template for creating contracts for customers. There are multiple rows (of varying quantity) of items we provide to our customers. We charge a fee to maintain certain items. Those Items are represented by a certain class (R22:102). If there are none of the classes that get charged are present the maintain inventory fee line is removed (first IF above). If not the min charge (Column M) for each of those items needs to be totaled and multiplied by a percentage that changes from customer to customer (the Code identifying what percentage to use lives in (R103)) Below is what I think it should look like except for the parts where I have no stinkin clue how to word it.

Code: 
[B]If WorksheetFunction.CountIf(Range("Q22:Q102"), "4") = 0 Then
Range("A103").EntireRow.Delete
Else
                Foreach Cell in Range(“R22:R102”)
                                If Cell.Value = “07”, “09”, “11”, “12”, “13”, “14” Then[/B]

[I]Take the value in column M in each row and add it to some grand total (maybe off in cell S1).[/I]
[I]THEN take that total and multiply it by a percentage determined by a number in[/I]  R103.
                                [I]Here is kindof how I see that:
[/I]
[B]If Range(“R103”).Cell.Value = 02 Then[/B]
[I]                Multiply that grand total by 2%[/I]
[B]Elseif Range(“R103”).Cell.Value = 03 Then[/B]
[I]                By 3%[/I]
[B]Elseif Range(“R103”).Cell.Value = 04 Then[/B]
[I]                By 5%[/I]
[B]Elseif Range(“R103”).Cell.Value = 05 Then[/B]
[I]                By 5%[/I]
[B]Elseif Range(“R103”).Cell.Value = 06 Then[/B]
[I]                By 6%[/I]
[B]Elseif Range(“R103”).Cell.Value = 08 Then[/B]
[I]                By 8%[/I]
([B]Elseif Range(“R103”).Cell.Value = 09 Then[/B]
[I]                By 3%) [U]this one can only apply to “14” and “09” above.[/U][/I]
[B]Elseif Range(“R103”).Cell.Value = 4A Then[/B]
[I]                By 4%
[/I]
[I]And finaly That result needs to live in cell (M103)
[/I]

Thanks In advance!
Joe.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
What about a SELECT CASE statement? For instance:
SELECT CASE range("R103)
CASE 4: Percentage = 4%
CASE 6: Percentage = 6%
CASE 8: Percentage = 8%
CASE Else: Percentage= 0%
END SELECT

And at the end you multiply your grand total:

Range("M103") = Range("M103") * (1+Percentage)
 
Upvote 0
Hi Manu197a!
I've never used or seen used a select case statement.
Case x -> so x here would be the contents of
Cell R103. So percentage is then a variable that is set depending on what 'case' proves true.
Am I understanding that correctly?

Those last two lines have me completely confused.

Thanks!
Joe
 
Upvote 0
Joeslost said:
Hi Manu197a!
I've never used or seen used a select case statement.
Case x -> so x here would be the contents of
Cell R103. So percentage is then a variable that is set depending on what 'case' proves true.
Am I understanding that correctly?

Those last two lines have me completely confused.

Thanks!
Joe
Click to expand...

Indeed. The variable Percentage (you can rename it as you wish) will take a value depending on the content of R103.

Once you have the correct value for Percentage you can use it to multiply the grand total.

Range("M103") = Range("M103") * (1+Percentage) <-- this one might have confused you. If you have your grand total in the cell M103 (lets say $1,000), you multiply that ammount by 1+Percentage (lets say 1+4% = 1.04). And the new value in the cell M103 will be the ammount with the appropiate rate ($1,040).

But, if you only want the cell M103 to show the percentage without multiplying the gran total, the right code would be:
Range("M103") = Percentage
 
Last edited:
Upvote 0
Ah I see! That all makes sense. So now how can I go about getting my total of only the lines that meet the class criteria?
 
Upvote 0
Alright so here is how i'm thinking this will look. In bold are the bits i'm stil not certain how to work out.

Code: 
Dim Run as integer
Dim Chr(1 to 80) as integer
Dim I as integer
Dim X as integer


If WorksheetFunction.CountIf(Range("Q22:Q102"), "4") = 0 Then
  Range("A103").EntireRow.Delete
  Set run as "0"
Else
  Foreach Cell in Range("R22:R102")
    For X as 1 to 80
[B]  ???Set (row #) as I[/B]
      If Cell.Value = “07”, “09”, “11”, “12”, “13”, “14” Then
        Set chr(X) as Range("M(I)").Cell.Value
        Set Run as "1"
      Else 
        Set chr(x) as "0"
        Set Run as "1"
    Next
End If


If Run = "1" Then
  Select Case Range("R103")
    Case 02: Per = 2%
    Case 03: Per = 3%
    Case 04: Per = 5%
    Case 05: Per = 5%
    Case 06: Per = 6%
    Case 08: Per = 8%
    Case 09: Per = 3%
    Case 4A: Per = 4%
    Case Else: Per = 0%
  End Select
[B]??Range("M103") = Chr(1 to 80) * (Per)
[/B]End If

Thanks Again!
 
Upvote 0
Here I have cleaned up the code and I believe have found solutions to all of my problems save for one.
Code: 
Dim Run As Integer
Dim Chr(1 To 80) As Integer
Dim I As Integer
Dim X As Integer
If WorksheetFunction.CountIf(Range("Q22:Q102"), "4") = 0 Then
  Range("A103").EntireRow.Delete
  Run = "0"
Else
  For Each Cell In Range("R22:R102")
  For X = 1 To 80
  I = ActiveCell.Row
  If Cell.Value = “07” Or “09” Or “11” Or “12” Or “13” Or “14” Then
    Chr(X) = Range("M(I)").Cell.Value
    Run = "1"
  Else
    Chr(X) = "0"
    Run = "1"
  End If
  Next
  Next
End If


If Run = "1" Then
  Select Case Range("R103")
    Case "02": Per = 0.08
    Case "03": Per = 0.12
    Case "04": Per = 0.05
    Case "05": Per = 0.2
    Case "06": Per = 0.24
    Case "08": Per = 0.32
    Case "09": Per = 0.12
    Case "4A": Per = 0.16
    Case Else: Per = 0
  End Select
  
  For r = LBound(Chr, 1) To UBound(Chr, 1)
    Chr(r) = Chr(r) * Per
  Next r
  Range("M103").Cells.Value = WorksheetFunction.Sum(arrayX)
End If
This hangs up on
Code: 
Chr(X) = Range("M(I)").Cell.Value
with an error: Run-TimeError '1004': Method 'Range' of object '_global' failed.
How should this be worded?
Thanks!
Joe
 
Last edited:
Upvote 0
Okay I have Corrected
Code: 
[COLOR=#333333]Chr(X) = Range("M(I)").Cell.Value
[B]TO
[/B]Chr(X) = Range("M" & ActiveCell.Row).Cell.Value
[/COLOR]
Taking The Variable I out of this mix and making things cleaner.
However now results in error: Run-Time Error '438': Object Doesn't support this property or method. What have I done wrong?
 
Upvote 0
Hello Again!
I have made further progress and eliminated all errors! Only...there are no results, somehow it is not doing what it is supposed to and I have hit a wall. Any insight would be appreciated!

Code: 
Dim Run As Integer
Dim Chr(1 To 80) As Integer
Dim X As Integer
If WorksheetFunction.CountIf(Range("Q22:Q102"), "4") = 0 Then
  Range("A103").EntireRow.Delete
  Run = "0"
Else
  For Each cell In Range("R22:R102")
  For X = 1 To 80
  cell.Select
  If cell.Value = “07” Or “09” Or “11” Or “12” Or “13” Or “14” Then
    Chr(X) = Range("M" & ActiveCell.Row).Value
    Run = "1"
  Else
    Chr(X) = "0"
    Run = "1"
  End If
  Next X
  Next
End If


If Run = "1" Then
  Select Case Range("R103")
    Case "02": per = 0.08
    Case "03": per = 0.12
    Case "04": per = 0.05
    Case "05": per = 0.2
    Case "06": per = 0.24
    Case "08": per = 0.32
    Case "09": per = 0.12
    Case "4A": per = 0.16
    Case Else: per = 0
  End Select


  For r = LBound(Chr, 1) To UBound(Chr, 1)
    Chr(r) = Chr(r) * per
  Next r
  Range("M103").Value = WorksheetFunction.Sum(Chr)
End If

I also Came up with something to Accomplish my needing case 09 only to apply for class 14 an 09 using functions. But I think this wont work because the variables wont carry into the functions????

Code: 
Dim Chr(1 To 80) As Integer
Dim X As Integer


Select Case Range("R103")
  Case "02":
    Per = 0.08
    Defaultcases
  Case "03": 
    Per = 0.12
    Defaultcases
  Case "04":
    Per = 0.05
    Defaultcases
  Case "05":
    Per = 0.2
    Defaultcases
  Case "06": 
    Per = 0.24
    Defaultcases
  Case "08": 
  Per = 0.32
  Defaultcases
  Case "09": 
    Per = 0.12
    Case09
  Case "4A": 
    Per = 0.16
    Defaultcases
  Case Else: 
  Per = 0
  Nocase
End Select
End Sub


function Case09
  For Each Cell In Range("R22:R102")
  For X = 1 To 80
    Chr(X) = Range("M" & ActiveCell.Row).Value


    For r = LBound(Chr, 1) To UBound(Chr, 1)
      Chr(r) = Chr(r) * Per
    Next r
    Range("M103").Cells.Value = WorksheetFunction.Sum(chr)
  Next
  Next
Exit Function


Function Defaultcases
For Each Cell In Range("R22:R102")
  For X = 1 To 80
  If Cell.Value = “07” Or “09” Or “11” Or “12” Or “13” Or “14” Then
    Chr(X) = Range("M" & ActiveCell.Row).Value
    
    For r = LBound(Chr, 1) To UBound(Chr, 1)
      Chr(r) = Chr(r) * Per
    Next r
    Range("M103").Value = WorksheetFunction.Sum(chr)
  Next
  Next
Exit Function


Function Nocase
Range("A103").EntireRow.Delete
Exit Function
I dont think I can progress any further in the problem alone.
Once again Thanks to any willing to give insights!
Joe
 
Last edited:
Upvote 0
Hello Again.
I am completely at a loss. I have polished up everything to where I just cannot understand where I have gone wrong here.


I Have a document that activates case 09.
Results: it transfers every value in range in column M to S regardless of whether it equates to true for the If statement. I get no results in M103, so Im not sure If Chr is established, or if it is multiplied by Per, or if it is even summed

Code: 
Dim Chr(1 To 80) As Integer
Dim X As Integer

Select Case Range("R103")
  Case "02":
    Range("S21") = "Case 02"
    Per = 0.08
    For Each cell In Range("R22:R102")
      If cell.Value = “07” Or “09” Or “11” Or “12” Or “13” Or “14” Then
      Range("S" & ActiveCell.Row).Value = Range("M" & ActiveCell.Row).Value
      End If
    Next
    
  Case "03":
    Range("S21") = "Case 03"
    Per = 0.12
    For Each cell In Range("R22:R102")
      If cell.Value = “07” Or “09” Or “11” Or “12” Or “13” Or “14” Then
      Range("S" & ActiveCell.Row).Value = Range("M" & ActiveCell.Row).Value
      End If
    Next
    
  Case "04":
    Range("S21") = "Case 04"
    Per = 0.05
    For Each cell In Range("R22:R102")
      If cell.Value = “07” Or “09” Or “11” Or “12” Or “13” Or “14” Then
      Range("S" & ActiveCell.Row).Value = Range("M" & ActiveCell.Row).Value
      End If
    Next
    
  Case "05":
    Range("S21") = "Case 05"
    Per = 0.2
    For Each cell In Range("R22:R102")
      If cell.Value = “07” Or “09” Or “11” Or “12” Or “13” Or “14” Then
      Range("S" & ActiveCell.Row).Value = Range("M" & ActiveCell.Row).Value
      End If
    Next
    
  Case "06":
    Range("S21") = "Case 06"
    Per = 0.24
    For Each cell In Range("R22:R102")
      If cell.Value = “07” Or “09” Or “11” Or “12” Or “13” Or “14” Then
      Range("S" & ActiveCell.Row).Value = Range("M" & ActiveCell.Row).Value
      End If
    Next
    
  Case "08":
  Range("S21") = "Case 08"
  Per = 0.32
  For Each cell In Range("R22:R102")
    If cell.Value = “07” Or “09” Or “11” Or “12” Or “13” Or “14” Then
    Range("S" & ActiveCell.Row).Value = Range("M" & ActiveCell.Row).Value
    End If
  Next
  
  Case "09":
    Range("S21") = "Case 09"
    Per = 0.12
    For Each cell In Range("R22:R102")
      If cell = "09" Or "14" Then
      cell.Select
      Range("S" & ActiveCell.Row).Value = Range("M" & ActiveCell.Row).Value
      End If
    Next
    
  Case "4A":
    Range("S21") = "Case 4A"
    Per = 0.16
    For Each cell In Range("R22:R102")
        cell.Select
      If cell.Value = "7" Or "9" Or "11" Or "12" Or "13" Or "14" Then
      Range("S" & ActiveCell.Row).Value = Range("M" & ActiveCell.Row).Value
      End If
    Next
    
  Case Else:
    Range("S21") = "Case NO"
    Per = 0
    Range("A103").EntireRow.Delete
    GoTo Failed
End Select


Range("S19") = Per
Char = Range("S22:S102")


For r = LBound(Chr, 1) To UBound(Chr, 1)
  Chr(r) = Chr(r) * Per
Next r


Range("M103").Value = WorksheetFunction.Sum(Chr)


GoTo Finished




Failedcell:
Range("S20") = ("Failedcell")
GoTo DONEXT
Failed:
Range("S20") = ("Failed")
GoTo DONEXT
Finished:
Range("S20") = ("Finished")


DONEXT:

Any help is greatly appreciated!!!'

Thanks,
A broken Man..
 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Issues with Excel 2013
Replies
0
Views
3K
Hawk3116
H

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top