Sleeper: Sum Range

[stapuff]

How would the following be properly written to sum 2 ranges in a macro?

I've been screwing around with this far too long.

ElseIf Application.WorksheetFunction.Sum(Range(c.Offset(-24, 2).Address, (c.Offset(-1, 2).Address))) = 0 Then

Thanks,

Kurt

[Zack Barresse]

Hey Kurt,

Don't use the .Address property, use Range objects ...

ElseIf WorksheetFunction.Sum(c.Offset(-24, 2), c.Offset(-1, 2)) = 0 Then

.. should work for you.

[stapuff]

Heya Zack,

I had tried that already. Getting a App or Object defined error.

Thanks,

Puff

[mvidas]

Hi Puff,

What address does c have in your above example? It must be at least in row 25, as the -24 offset rows will give you an error..
Matt

[stapuff]

mvidas,


Just figured that out after posting. You are correct. I am at line 9.

Now I need to figure out a error handler to fix this. On error resume next will not work for me.

Something like On error goto next elseif. I know that doesn't work, but something like that.

Until I get far enough down the range I will get the error. I can't really change the statements around. I want to check for worst case and least likely scenario first.

Any suggestions.

Thanks,

Puff

[Aaron Blood]

What are you doing here? Like a running sum or something like that?

[Zack Barresse]

I agree with Aaron here Kurt. Explain what you are trying to do, and a better solution may present itself.

[stapuff]

Aaron -

Thanks for the post back.

I am attempting a solver loop. I need to sum a range to get the solver to work the way I need it to. Check for least possible first. I have added everything that I want to check.

Your thoughts -

Kurt

Sub optprodn()
  Dim c As Variant
  Dim rng As Range
  Dim i As Long
  Sheets("sheet1").Select
  i = Range("AS3").Row
  While Cells(i, 45) <> "Week"
  i = i + 1
  Wend
  ActiveSheet.Range("AV" & i + 1 & ":AV300").ClearContents
  Sheets("sheet1").Select
  Set rng = Range([AS9], [AS9].End(xlDown))
  For Each c In rng
  If c.Value = "Week" Then
  If c.Offset(0, 2).Value > 0 Then
  If c.Offset(0, 2).Value < Range("$A$20").Value Then
  c.Offset(0, 3) = "1"
              ElseIf WorksheetFunction.Sum(c.Offset(-24, 2), c.Offset(-1, 2)) = 0 Then
                  a = c.Offset(0, 2).Value
                  SolverReset
                  SolverOk SetCell:=c.Offset(0, 5), MaxMinVal:=3, ValueOf:=a, ByChange:= _
                  c.Offset(-24, 3).Address & ":" & c.Offset(0, 3).Address
 SolverAdd CellRef:=c.Offset(-24, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=1, FormulaText:="3"
 SolverAdd CellRef:=c.Offset(-24, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=3, FormulaText:="0"
 SolverAdd CellRef:=c.Offset(-24, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=4, FormulaText:="integer"
                  SolverSolve UserFinish:=True
              ElseIf WorksheetFunction.Sum(c.Offset(-19, 2), c.Offset(-1, 2)) = 0 Then
                  a = c.Offset(0, 2).Value
                  SolverReset
                  SolverOk SetCell:=c.Offset(0, 5), MaxMinVal:=3, ValueOf:=a, ByChange:= _
                  c.Offset(-19, 3).Address & ":" & c.Offset(0, 3).Address
 SolverAdd CellRef:=c.Offset(-19, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=1, FormulaText:="3"
 SolverAdd CellRef:=c.Offset(-19, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=3, FormulaText:="0"
 SolverAdd CellRef:=c.Offset(-19, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=4, FormulaText:="integer"
                  SolverSolve UserFinish:=True
              ElseIf WorksheetFunction.Sum(c.Offset(-14, 2), c.Offset(-1, 2)) = 0 Then
                  a = c.Offset(0, 2).Value
                  SolverReset
                  SolverOk SetCell:=c.Offset(0, 5), MaxMinVal:=3, ValueOf:=a, ByChange:= _
                  c.Offset(-14, 3).Address & ":" & c.Offset(0, 3).Address
 SolverAdd CellRef:=c.Offset(-14, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=1, FormulaText:="3"
 SolverAdd CellRef:=c.Offset(-14, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=3, FormulaText:="0"
 SolverAdd CellRef:=c.Offset(-14, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=4, FormulaText:="integer"
                  SolverSolve UserFinish:=True
              ElseIf WorksheetFunction.Sum(c.Offset(-9, 2), c.Offset(-1, 2)) = 0 Then
                  a = c.Offset(0, 2).Value
                  SolverReset
                  SolverOk SetCell:=c.Offset(0, 5), MaxMinVal:=3, ValueOf:=a, ByChange:= _
                  c.Offset(-9, 3).Address & ":" & c.Offset(0, 3).Address
 SolverAdd CellRef:=c.Offset(-9, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=1, FormulaText:="3"
 SolverAdd CellRef:=c.Offset(-9, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=3, FormulaText:="0"
 SolverAdd CellRef:=c.Offset(-9, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=4, FormulaText:="integer"
                  SolverSolve UserFinish:=True
              Else
                  a = c.Offset(0, 2).Value
                  SolverReset
                  SolverOk SetCell:=c.Offset(0, 5), MaxMinVal:=3, ValueOf:=a, ByChange:= _
                  c.Offset(-4, 3).Address & ":" & c.Offset(0, 3).Address
 SolverAdd CellRef:=c.Offset(-4, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=1, FormulaText:="3"
 SolverAdd CellRef:=c.Offset(-4, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=3, FormulaText:="0"
 SolverAdd CellRef:=c.Offset(-4, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=4, FormulaText:="integer"
                  SolverSolve UserFinish:=True
              End If
          End If
      End If
  Next c
  End Sub

[stapuff]

I am going to try to explain what I am doing a little better.

Example will be row 18....

AQ Is a column of dates (current day through May) (Monday-Friday dates only) Formatted 1/21/2005.
AR is Day of week Monday, Tuesday,Wednesday etc...
In column AS18 is the word "Week" . AT18 is the qty of the order. (How many pieces they want us to ship). In AU18 in a trunc formula that looks at AT18/$A$20. $A$20 is how many piece we can make a shift. $A$20 = 451.
AV18 is where the solver is going to put the # of shifts a day we need to make parts on. AW18 is going to be the sum the solver will use.

My row loop takes me down to row AV9 then clears content from AV9 to AV300(old shifts needed range).

I set my range of AS9 down (the column with the word "Week" in it).


Still sticking with column 18 as the example.....This is what I want it to do.

If AS18 = "Week" check to see if the value of AU18>0 if not Next c, if so check to see if AU18> $A$20 if not AV18 = 1 (I need to produce less than 1 shift) if AU18 is>$A$20 than check to see if AU17 through "error" = 0. If true used the whole range to divid you customer order by else Next else if statement.

I understand I have built in errors hence the need for a handler of some type.

To best descrbe it in laymens' terms....

If your customer places a very large order for widgets for the end of March - you should be starting to produce them before you need them. If you wait you may have constraints(capacity,machines,manpower,etc) that you can not overcome. The macro currently looks at a 5 day window and determines how many shifts of production you are going to need to produce on (which is the last else statement) in my code. If the week before that has no orders you should be looking at both weeks to plan production (which is my last elseif statement). If the week before that even has no orders - now you should be looking at 3 weeks to schedule production. (which is my 2nd to last elseif statement) and so on going up to 5 weeks (my first elseif statement).

By doing this you could see what day would be the best to start producing the widgets on without major concern. I would rather start producing something 4 weeks early at 1 shift a day (with little or no issues) than waiting and increasing shifts needed to produce the same order.

Thanks,

Kurt

[stapuff]

Instead of error trapping I am checking the row # to run the correct statement.

When I ran my macro - the correct statement was picked, but the original problem for this post is still there (Sum of a range). If WorksheetFunction.Sum(c.Offset(-9, 2), c.Offset(-1, 2)) = 0 Then is not working. The sum for the range was not 0 yet the code ran.

Any suggestions?

Thanks again for your time,

Kurt

ElseIf c.Row >= 10 Then
              If WorksheetFunction.Sum(c.Offset(-9, 2), c.Offset(-1, 2)) = 0 Then
                  a = c.Offset(0, 2).Value
                  SolverReset
                  SolverOk SetCell:=c.Offset(0, 5), MaxMinVal:=3, ValueOf:=a, ByChange:= _
                  c.Offset(-9, 3).Address & ":" & c.Offset(0, 3).Address
                  SolverAdd CellRef:=c.Offset(-9, 3).Address & _
 ":" & c.Offset(0, 3).Address, Relation:=1, FormulaText:="3"
SolverAdd CellRef:=c.Offset(-9, 3).Address & _
 ":" & c.Offset(0, 3).Address, Relation:=3, FormulaText:="0"
SolverAdd CellRef:=c.Offset(-9, 3).Address & _
 ":" & c.Offset(0, 3).Address, Relation:=4, FormulaText:="integer"
SolverSolve UserFinish:=True
                  End If

[Jacob Hilderbrand]

The sum for the range was not 0 yet the code ran.

Try this.

x = c.Offset(-9, 2)
y = c.Offset(-1, 2)

MsgBox x
MsgBox y

Are the values correct? are the values in the cells actually text?

[stapuff]

DRJ -

I ran the following:

ElseIf c.Row > 20 Then
                                  x = WorksheetFunction.Sum(c.Offset(-19, 2), c.Offset(-1, 2))
 MsgBox x
            If WorksheetFunction.Sum(c.Offset(-19, 2), c.Offset(-1, 2)) = 0 Then
                 a = c.Offset(0, 2).Value
                 SolverReset
                 SolverOk SetCell:=c.Offset(0, 7), MaxMinVal:=3, ValueOf:=a, ByChange:= _
                 c.Offset(-19, 3).Address & ":" & c.Offset(0, 3).Address
                 SolverAdd CellRef:=c.Offset(-19, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=1, FormulaText:="3"
                 SolverAdd CellRef:=c.Offset(-19, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=3, FormulaText:="0"
                 SolverAdd CellRef:=c.Offset(-19, 3).Address & ":" & c.Offset(0, 3).Address, Relation:=4, FormulaText:="integer"
                 SolverSolve UserFinish:=True
                 End If

It found the "Week" value in row 23 so the ElseIf c.Row > 20 Then is "TRUE". The message box value shows 0 yet it is actually 11271 and not 0 yet the code ran. The column under format shows numbers not text.

Any other idea's?

Thanks,

Kurt

[stapuff]

DRJ -

In addition I first tried you X & Y message boxs - both showed 0 values.

Kurt

[Jacob Hilderbrand]

DRJ -

In addition I first tried you X & Y message boxs - both showed 0 values.

Kurt

Ok, try this. In a blank cell type in 1. Copy. Select your range of numbers. Edit | Paste Special | Multiply.

Try the macro again and see if it works.

Also if you use vba tags instead of code tags your code will be displayed much better. Just use (vba) (/vba) only with square brackets [].