A challenge!

[Joeslost]

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.

[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]

Hi Joe,
Sorry for not responding your posts. For some reason I did not get notifications.

An important thing to consider is if the cell R103 contains text or numbers. In excel as well as VBA is different 1 (number) from "1" (text). So, in the select case you are analyzing text (and not only "1" but "01"). If the cell R103 contains a number, you should change your code to "Case 1:", etc.

 

[Joeslost]

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

An important thing to consider is if the cell R103 contains text or numbers.

Hi Man197a! Thanks for the reply!

Actually all the values I am working with here are formatted as text, so "Case "09"" Is correct.
In my process of attempting to resolve this I have included in the code some proofs, or "exceptions" kinda.

For each case, I made it so It will print "Case XX" in cell S21. And that works.
So my case proves true.

I also Made it so it prints the value of "Per" in cell S19 once it is out of the select case statement.
So I know the % is properly assigned.

Then something strange happens. Even though most cells in Range("R22:R102") Fail the if statement:

If cell = "09" Or "14" Then
      cell.Select
      Range("S" & ActiveCell.Row).Value = Range("M" & ActiveCell.Row).Value
      End If

All of them act as if they were proven true. That is to say foreach cell the value in Mxx was copied to Sxx, regardless if Rxx = either "09" or "14".

From here one would think I would still get a result in M103, only it would be quite high as it somehow included things that were not intended to be included.
But no dice M103 remains empty.
One theory I have here is that this probably should through an exception like "typemismatch" because Per is a number, and all values in arry Chr are text.


If it would help you could PM me your email address and I can shoot both workbooks over to you for you to check out.

Thanks A lot!
Joe

 

[manu197a]

I think the issue is the if statement:

If cell = "09" Or "14" Then

This won't work properly as VBA needs a specific way of writing conditions:

If cell = "09" Or cell = "14" Then
https://www.google.com.mx/search?cl...&ved=0ahUKEwiLy83ViN_NAhVGjz4KHfjGBEcQBQgZKAA
Write me a private message to send you my mail.
Best

 

[Joeslost]

If cell = "09" Or cell = "14" Then

Manu197a,
Thanks! That seams to have fixed that issue.
But multiplying each by a % then summing them is still proving to be an issue

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)

From here one would think I would still get a result in M103, only it would be quite high as it somehow included things that were not intended to be included.
But no dice M103 remains empty.
One theory I have here is that this probably should through an exception like "typemismatch" because Per is a number, and all values in arry Chr are text.

Thanks again!
Joe

 

[manu197a]

One problem I see is that you have two different variables in the code: Char and Chr.

You cannnot use "Chr" as a variable because it is a VBA function. So when you try to assign a value to Chr(r) it causes an error.

This could be achieved with another method:

Range("S19") = Per

Range("S22:S102").Select

For Each Cell in selection
     Cell.value = Cell.value * Per
Next

Range("M103").Value = WorksheetFunction.Sum(Range("S22:S102"))