A challenge!

Joeslost · Jun 29, 2016

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.

manu197a · Jun 29, 2016

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)

Joeslost · Jun 29, 2016

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

manu197a · Jun 29, 2016

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

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

Joeslost · Jun 29, 2016

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?

Joeslost · Jun 30, 2016

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!

Joeslost · Jun 30, 2016

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

Joeslost · Jun 30, 2016

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?

Joeslost · Jun 30, 2016

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

Joeslost · Jul 1, 2016

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..

A challenge!

Joeslost

New Member

Excel Facts

manu197a

Board Regular

Joeslost

New Member

manu197a

Board Regular

Joeslost

New Member

Joeslost

New Member

Joeslost

New Member

Joeslost

New Member

Joeslost

New Member

Joeslost

New Member

Similar threads

Forum statistics

Share this page

A challenge!

New Member

Excel Facts

Board Regular

New Member

Board Regular

New Member

New Member

New Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock