I use Goal seek function in excel an wanted to automate it.
I have my goal hours in cell “G1” where I want to get all by picks completed by. In this example 5 hours.
I have in “ F3:F21” formula =$C$3*$E$3 and in cell “G3:G21” = =IFERROR(IF(D3>=C3,D3/F3,1),0)
When I use the Goal seek what if Analysis and enter the following :
Set Cell: G3
To Value: G1
By changing cell E3
When I do this for each cell everything calculates fine. It gives me how many pickers I would need to pick the Que Groups instructions for the Goal Hours to Finish Picks.
When I run my VBA look any numbers less then Hourly required to meet minimum are way off .
Here is both loops I tried:
UpdatedGoalSeek
<tbody>
</tbody>
Excel
and then this code :
tables to the web >> Excel Jeanie
HTML 4
Any one see what I am doing wrong with my code.
Do I need to code if, say D3 is less than C3 do not goal seek because any number less than gives me wrong long numbers.
Any help and direction as to what I am doing wrong is appreciated. Thanks
I have my goal hours in cell “G1” where I want to get all by picks completed by. In this example 5 hours.
I have in “ F3:F21” formula =$C$3*$E$3 and in cell “G3:G21” = =IFERROR(IF(D3>=C3,D3/F3,1),0)
When I use the Goal seek what if Analysis and enter the following :
Set Cell: G3
To Value: G1
By changing cell E3
When I do this for each cell everything calculates fine. It gives me how many pickers I would need to pick the Que Groups instructions for the Goal Hours to Finish Picks.
When I run my VBA look any numbers less then Hourly required to meet minimum are way off .
Here is both loops I tried:
UpdatedGoalSeek
A | B | C | D | E | F | G | H | |
1 | Goal Hours to Finish Picks | 5 | ||||||
2 | Que Group | Min Requirement | Hourly Required to Meet | QueGroup Instructions Breakdown | No of Pickers assigned | Hourly Picks Per Team | Meeting Min Hours Req to Complete | |
3 | DSLA | 162 | 27 | 27 | 1.0 | 27 | 1.0 | |
4 | ITTTQ | 162 | 27 | 196 | 1.0 | 27 | 7.3 | |
5 | T300 | 162 | 27 | 110 | 1.0 | 27 | 4.1 | |
6 | T500 | 162 | 27 | 160 | 1.0 | 27 | 5.9 | |
7 | T600 | 162 | 27 | 129 | 1.0 | 27 | 4.8 | |
8 | T700 | 140 | 23 | 140 | 1.0 | 23 | 6.0 | |
9 | TBULK | 114 | 19 | 92 | 1.0 | 19 | 4.8 | |
10 | TDK2D | 326 | 54 | 4 | 1.0 | 54 | 1.0 | |
11 | TMEZZ | 326 | 54 | 656 | 1.0 | 54 | 12.1 | |
12 | TPRCK | 162 | 27 | 112 | 1.0 | 27 | 4.1 | |
13 | TTURR | 120 | 20 | 49 | 1.0 | 20 | 2.5 | |
14 | TUMEZ | 326 | 54 | 53 | 1.0 | 54 | 1.0 | |
15 | TYFIT | 80 | 13 | 22 | 1.0 | 13 | 1.7 | |
16 | TYPIP | 80 | 13 | 7 | 1.0 | 13 | 1.0 | |
17 | TYTNK | 80 | 13 | 1 | 1.0 | 13 | 1.0 | |
18 | 1.0 | 0.0 | ||||||
19 | 1.0 | 0.0 | ||||||
20 | 1.0 | 0.0 | ||||||
21 | 1.0 | 0.0 | ||||||
22 |
<tbody>
</tbody>
Code:
Sub PickOptimizerGoalSeek()
Dim X As Integer
Dim i As Integer
X = Range("G1").Value
For i = 3 To 21
If Range("D" & i).Value <= Range("C" & i).Value Then
Range("G" & i).GoalSeek Goal:=X, ChangingCell:=Range("E" & i)
End If
Next i
End Sub
and then this code :
Code:
Sub PickGoalSeek()
Dim i As Integer
Dim j As Double
j = Range("G1").Value
For i = 3 To 21
If Range("D" & i).Value <> 0 Then
Range("G" & i).GoalSeek Goal:=j, ChangingCell:=Range("E" & i)
End If
Next i
End Sub
HTML 4
Any one see what I am doing wrong with my code.
Do I need to code if, say D3 is less than C3 do not goal seek because any number less than gives me wrong long numbers.
Any help and direction as to what I am doing wrong is appreciated. Thanks