Create a Minimum Function

[PJfan]

The company I work for is asking me to create a VBA Program that will allow us to determine what is the optimal cost. We have these control boxes and each box contains four switches. If one switch goes out the entire box goes down. We know that the cost to replace a switch is $200/switch plus $1000/hour the box is down. If we replace one swith at a time the box is down for one hour, but if we replace all four the box is down for two hours. I have created the code for replacing all four switches though I am having trouble with replacing one switch at a time. The switches can last anywhere from 1000 to 2000 hours.

I know that I need to create a function that will find the minimum value in the range, this being the row and subtracting all the other switches from the minimum. For example. Switch one lasts 1200 hours, Switch 2 1400 hours, Swithc 3 1600 hours, and Switch 4 is 1800 hours. I need the program to find the minimum value, and then subtract it out of all of the values. leaving me with, 0, 200, 400, 600 respectively. It then needs to generate a new random number for the switch that is 0. So the next period may look like 1150, 200, 400, 600. Then all the switches would be subtracted by 200 and so on. if anyone has a clue how to do this or can point me in the right direction, that would be great.

Thanks

Jeremy

 

[dwilson01]

Finding the minimum number and subtracting from the others is easy. My test file has in row 1 column A "Switch 1", row 1 column B "Switch 2" ... In row 2 Column A "1200", row 2 column B "1400" ...

The random number part I'm not sure I understand. I think what you want is a random number between 1000 and 2000.

Here is what I have come up with to this point. Let me know what you like or dislike about this.

Sub MinVal()

Dim oCell As Object, iMin As Long, rNum As Integer, Zero As Object

start:
ActiveSheet.UsedRange
iMin = Application.WorksheetFunction.Min(ActiveSheet.UsedRange)
Set Zero = ActiveSheet.UsedRange.Find(What:=iMin, LookAt:=xlWhole)
For Each oCell In ActiveSheet.UsedRange.Rows(Zero.Row).Cells
  If IsNumeric(oCell) Then
    oCell.Offset(1, 0) = oCell - iMin
  End If
Next
Set Zero = ActiveSheet.UsedRange.Find(What:=0, LookAt:=xlWhole)
rNum = Int((2000 - 1000 + 1) * Rnd + 1000)
Range(Zero.Address) = rNum
i = i + 1
If i >= 4 Then Exit Sub
GoTo start

End Sub