PDA

View Full Version : Sleeper: Sum Range



stapuff
01-21-2005, 12:48 PM
How would the following be properly written to sum 2 ranges in a macro? :dunno

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


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

Thanks,

Kurt

Zack Barresse
01-21-2005, 12:58 PM
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
01-21-2005, 01:04 PM
Heya Zack,

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

Thanks,

Puff

mvidas
01-21-2005, 01:06 PM
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
01-21-2005, 01:24 PM
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
01-21-2005, 01:33 PM
What are you doing here? Like a running sum or something like that?

Zack Barresse
01-21-2005, 01:48 PM
I agree with Aaron here Kurt. Explain what you are trying to do, and a better solution may present itself. :yes

stapuff
01-21-2005, 01:49 PM
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
01-21-2005, 06:25 PM
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
01-24-2005, 06:50 AM
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
01-24-2005, 07:05 AM
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
01-24-2005, 09:42 AM
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
01-24-2005, 09:43 AM
DRJ -

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

Kurt

Jacob Hilderbrand
01-24-2005, 03:16 PM
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 [].